﻿using System;
using System.Collections.Generic;
using System.Text;
using MES.DataGateway.DBUtility;
using System.Data;
using MES.DataGateway.Common;
using System.IO;
using System.Data.OracleClient;
using System.Collections;

namespace MES.DataGateway.OracleDAL
{
    public class StandardDAL
    {

        public DataSet GetSpecInfo(string specRevsoin, string specName)
        {
            string strSql = string.Format("select t.specid,t.specrevision,sb.specname from spec t left join specbase sb on sb.specbaseid=t.specbaseid " +
                                          "where t.specrevision='{0}'  and sb.specname='{1}'", specRevsoin, specName);

            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataSet GetSpecInfo(string specName)
        {
            string strSql = string.Format("select sb.specname,sb.revofrcdid from specbase sb where  sb.specname='{0}'", specName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public string GetSpecVersion(string specName)
        {
            string strVer = "";

            string strSql = string.Format("select sb.specname,sb.revofrcdid from specbase sb where  sb.specname='{0}'", specName);
            DataSet tmpSet = OracleHelper.Query(strSql);

            if (tmpSet.Tables[0].Rows.Count > 0)
            {
                strSql = string.Format("select t.specrevision from spec t where t.specid='{0}'", tmpSet.Tables[0].Rows[0]["revofrcdid"].ToString().Trim());
                DataSet tmp1Set = OracleHelper.Query(strSql);

                if (tmp1Set.Tables[0].Rows.Count > 0)
                {
                    strVer = tmp1Set.Tables[0].Rows[0]["specrevision"].ToString().Trim();
                }
            }

            return strVer;
        }

        public DataSet GetDocumentSetInfo(string strDocumentSetName)
        {
            string strSql = string.Format("select t.documentsetname from documentset t where t.documentsetname='{0}'", strDocumentSetName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;

        }

        public DataSet GetDocumentInfo(string strDocumentName, string strDocumentVersion)
        {
            string strSql = string.Format("select t.documentid from document t left join documentbase dbs on dbs.documentbaseid=t.documentbaseid where " +
                                         "t.documentrevision='{0}' and dbs.documentname='{1}'", strDocumentVersion, strDocumentName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public string GetDocumentVersionInfo(string strDocumentName)
        {
            string strRev = "";
            string strSql = string.Format("select t.revofrcdid from documentbase t where t.documentname='{0}'", strDocumentName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            if (tmpSet.Tables[0].Rows.Count > 0)
            {
                strSql = string.Format("select t.documentrevision from document t where t.documentid='{0}'", tmpSet.Tables[0].Rows[0]["revofrcdid"].ToString().Trim());
                DataSet verSet = OracleHelper.Query(strSql);
                if (verSet.Tables[0].Rows.Count > 0)
                {
                    strRev = verSet.Tables[0].Rows[0]["documentrevision"].ToString().Trim();
                }
            }
            return strRev;
        }

        public DataSet GetWorkflowInfo(string workflowName, string workFlowVersion)
        {
            string strSql = string.Format("select t.workflowid,t.ITEM_ID,t.PROCESS_NO from workflow t left join workflowbase wb on wb.workflowbaseid=t.workflowbaseid " +
                                         "where wb.workflowname='{0}'  and t.workflowrevision='{1}'", workflowName, workFlowVersion);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }
        public bool UpdateWorkflowInfo(string workflowID, string workFlowVersion)
        {
            string strSql = string.Format("Update WORKFLOW set workflowrevision='{0}' ,workflowtype='追加子工艺' where workflowid='{1}'", workFlowVersion, workflowID);
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }

        public DataSet GetWorkflowStepsByItemID(string itemID, string processNO, string revision)
        {
            string strSql = string.Format("SELECT WFS.WORKFLOWID,WFS.WORKFLOWSTEPID, WFS.WORKFLOWSTEPNAME,WFS.SUBWORKFLOWBASEID,WFS.SUBWORKFLOWID,WFS.SEQUENCE,S.SPECREVISION,SB.SPECNAME  FROM WORKFLOWSTEP WFS " +
                                         "  LEFT JOIN WORKFLOW WF  ON WFS.WORKFLOWID = WF.WORKFLOWID" +
                                         "  LEFT JOIN SPEC S ON WFS.SPECID = S.SPECID" +
                                         "  LEFT JOIN SPECBASE SB ON S.SPECBASEID = SB.SPECBASEID " +
                                         " WHERE WF.WORKFLOWREVISION='{2}' AND (WF.ITEM_ID='{0}' OR WF.PROCESS_NO='{1}')", itemID, processNO, revision);
            //Modify by Yuxj20181019
            strSql += " ORDER BY WFS.WORKFLOWID DESC,WFS.SEQUENCE asc ";
            DataSet tmpSet = OracleHelper.Query(strSql);

            return tmpSet;
        }

        public DataSet GetWorkflowStepsByyWorkFlowID(string workFlowID)
        {
            string strSql = string.Format("SELECT WFS.WORKFLOWSTEPID, WFS.WORKFLOWSTEPNAME,WFS.SUBWORKFLOWBASEID,WFS.SUBWORKFLOWID,WFS.SEQUENCE  FROM WORKFLOWSTEP WFS " +
                                            " WHERE WFS.WORKFLOWID='{0}' ", workFlowID);
            DataSet tmpSet = OracleHelper.Query(strSql);

            return tmpSet;
        }

        /// <summary>
        /// 根据工艺名字和版本获取工序
        /// add:Wangjh 20180926
        /// </summary>
        /// <param name="workflowname"></param>
        /// <param name="revision"></param>
        /// <returns></returns>
        public DataSet GetWorkflowStepByWfNameAndRev(string workflowname, string revision)
        {
            string strSql = @" SELECT STD.DURATIONPERUNIT,SUBSTR(WS.WORKFLOWSTEPNAME,0,INSTR(WS.WORKFLOWSTEPNAME,'-')-1) specno,w.workflowid,
ws.workflowstepid,ws.workflowstepname,ws.workcenterid,ws.teamid,
ws.manhours,ws.wipmsglabel,s.specid,sb2.specname FROM workflowstep ws
LEFT JOIN workflow w ON w.workflowid=ws.workflowid
LEFT JOIN Workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = WS.WORKFLOWSTEPID
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
left join specbase sb2 on sb2.specbaseid=s.specbaseid
            ";
            strSql += string.Format("where wb.workflowname='{0}' AND w.workflowrevision='{1}'", workflowname, revision);
            return OracleHelper.Query(strSql);
        }

        /// <summary>
        /// 获取含有工时或工区或班组或商品工时的工序根据Process_No
        /// add:Wangjh 20180926
        /// </summary>
        /// <param name="processNo"></param>
        /// <returns></returns>
        public DataSet GetWorkcenterOrTeamByProcessNo(string processNo)
        {
            string strSql = @" SELECT  STD.DURATIONPERUNIT,ws.*  FROM workflow w 
             LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
             LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = WS.WORKFLOWSTEPID
             WHERE  (STD.DURATIONPERUNIT is not null OR ws.WORKCENTERID IS NOT NULL OR ws.TEAMID IS NOT NULL OR ws.manhours IS NOT NULL)";
            strSql += string.Format(" AND w.process_no='{0}'", processNo);
            strSql += " ORDER BY SUBSTR(WS.WORKFLOWSTEPNAME,0,INSTR(WS.WORKFLOWSTEPNAME,'-')-1) ASC,WS.WORKFLOWSTEPID DESC";
            return OracleHelper.Query(strSql);
        }

        public void GetAssembleStepByWorkFlowID(string workFlowID, ref string strSpecName, ref string strSpecVersion)
        {
            string strSql = string.Format("select t.* from workflowstep t where t.workflowid='{0}'", workFlowID);
            DataSet stepSet = OracleHelper.Query(strSql);
            string strReturn = "";
            foreach (DataRow row in stepSet.Tables[0].Rows)
            {
                if (row["workflowstepname"].ToString().Trim().IndexOf("装配") != -1)
                {
                    if (row["specid"].ToString().Trim() != "0000000000000000")
                    {
                        strSql = string.Format("select t.specrevision,sb.specname from spec t " +
                                               "left join specbase sb on sb.specbaseid=t.specbaseid where t.specid='{0}'", row["specid"].ToString().Trim());

                        DataSet specSet = OracleHelper.Query(strSql);
                        strSpecName = specSet.Tables[0].Rows[0]["specname"].ToString().Trim();
                        strSpecVersion = specSet.Tables[0].Rows[0]["specrevision"].ToString().Trim();
                    }
                    else
                    {
                        strSql = string.Format("select t.specname,t.revofrcdid,s.specrevision from specbase t " +
                                               "left join spec s on s.specid=t.revofrcdid where t.specbaseid='{0}'", row["specbaseid"].ToString().Trim());
                        DataSet specBaseSet = OracleHelper.Query(strSql);
                        strSpecName = specBaseSet.Tables[0].Rows[0]["specname"].ToString().Trim();
                        strSpecVersion = specBaseSet.Tables[0].Rows[0]["specrevision"].ToString().Trim();
                    }
                    break;
                }
            }

        }

        public DataSet GetWorkflowInfoByName(string workflowName)
        {
            string strSql = string.Format("SELECT WB.WORKFLOWNAME, WF.WORKFLOWREVISION  FROM WORKFLOWBASE WB  LEFT JOIN WORKFLOW WF " +
                "  ON WF.WORKFLOWID = WB.REVOFRCDID where wb.workflowname='{0}'", workflowName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public string GetWorkFlowVersion(string workFlowName)
        {
            string strRev = "";
            string strSql = string.Format("select wb.workflowname,wb.revofrcdid from  workflowbase wb where wb.workflowname='{0}'", workFlowName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            if (tmpSet.Tables[0].Rows.Count > 0)
            {
                strSql = string.Format("select t.workflowrevision from workflow t where t.workflowid='{0}'", tmpSet.Tables[0].Rows[0]["revofrcdid"].ToString().Trim());
                DataSet verSet = OracleHelper.Query(strSql);
                if (verSet.Tables[0].Rows.Count > 0)
                {
                    strRev = verSet.Tables[0].Rows[0]["workflowrevision"].ToString().Trim();
                }
            }
            return strRev;
        }
        /// <summary>
        /// 获取零件组别件
        /// </summary>
        /// <param name="strProductName"></param>
        /// <param name="strProductRevision"></param>
        /// <returns></returns>
        public DataTable GetProductZBJ(string strProductName, string strProductRevision)
        {
            string strSql = @"SELECT pz.productrevision,pbz.productname FROM product p 
                                    LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                                    LEFT JOIN product pz ON pz.workflowid = p.workflowid
                                    LEFT JOIN producttype pt ON pt.producttypeid=pz.producttypeid
                                    LEFT JOIN productbase pbz ON pbz.productbaseid=pz.productbaseid";

            strSql = string.Format("{0} WHERE pb.productname='{1}'  AND p.productrevision='{2}' AND pt.producttypename='组别件'", strSql, strProductName, strProductRevision);

            DataTable dt = new DataTable();
            try
            {
                dt = OracleHelper.Query(strSql).Tables[0];
            }
            catch { }
            return dt;
        }

        public DataSet GetProduct(string strProductName)
        {
            string strSql = string.Format("select t.productname from productbase t where t.productname='{0}'", strProductName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public string GetProductCurrencyVersion(string strProductName)
        {
            string strVersion = "";
            string strSql = string.Format("select t.productname,t.revofrcdid from productbase t where t.productname='{0}'", strProductName);
            DataSet tmpSet = OracleHelper.Query(strSql);

            if (tmpSet.Tables[0].Rows.Count > 0)
            {
                strSql = string.Format("select t.productrevision from product t where t.productid='{0}'", tmpSet.Tables[0].Rows[0]["revofrcdid"].ToString().Trim());
                DataSet revSet = OracleHelper.Query(strSql);

                if (revSet.Tables[0].Rows.Count > 0)
                {
                    strVersion = revSet.Tables[0].Rows[0]["productrevision"].ToString().Trim();
                }
            }

            return strVersion;

        }

        public DataSet GetProductType(string strProductType)
        {
            string strSql = string.Format("select t.* from producttype t where t.producttypename='{0}'", strProductType);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataSet GetProductFamily(string strProductFamily)
        {
            string strSql = string.Format("select t.* from productfamily t where t.productfamilyname='{0}'", strProductFamily);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        /// <summary>
        /// 根据分厂代码获取分厂名称
        /// YangSJ 2017-11-10
        /// </summary>
        /// <param name="factoryCode"></param>
        /// <returns></returns>
        public string GetFacorybyCode(string factoryCode)
        {
            string strSql = @"select f.factoryname,ua.attributevalue,ua.userattributename from factory f 
                                left join userattribute ua on ua.parentid =f.factoryid 
                                where ua.userattributename='分厂代码'";
            strSql += string.Format("and ua.attributevalue='{0}'", factoryCode);
            DataSet myDS = OracleHelper.Query(strSql.ToString());
            if (myDS == null)
            {
                return factoryCode;
            }
            if (myDS.Tables.Count == 0)
            {
                return factoryCode;
            }
            if (myDS.Tables[0].Rows.Count == 0)
            {
                return factoryCode;
            }

            string factoryname = myDS.Tables[0].Rows[0]["factoryname"].ToString();
            return factoryname;
        }

        public DataSet GetNDOTable(string strTable, string strName, string strValue)
        {
            string strSql = string.Format("select t.* from {0} t where t.{1}='{2}'", strTable, strName, strValue);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataTable GetRDOTable(string strTable, string strName, string strNameValue, string strRev, string strRevValue)
        {
            string strSql = string.Format("select r.* from {0} r left join {0}base rb on r.{0}baseid=rb.{0}baseid " +
                    "  where  rb.{1}='{2}' and r.{3} ='{4}'", strTable, strName, strNameValue, strRev, strRevValue);
            DataSet tmpSet = OracleHelper.Query(strSql);
            if (tmpSet == null)
                return new DataTable();
            if (tmpSet.Tables.Count == 0)
                return new DataTable();

            return tmpSet.Tables[0];
        }
        public string GetNDOID(string strTable, string strName, string strNameValue)
        {
            string strSql = string.Format("select r.{0}ID from {0} r  where  r.{1}='{2}' ", strTable, strName, strNameValue);
            object obj = OracleHelper.GetSingle(strSql);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }
        public string GetRDOID(string strTable, string strName, string strNameValue, string strRev, string strRevValue)
        {
            string strSql = string.Format("select r.{0}ID from {0} r inner join {0}base rb on r.{0}baseid=rb.{0}baseid " +
                    "  where  rb.{1}='{2}' and r.{3} ='{4}'", strTable, strName, strNameValue, strRev, strRevValue);
            object obj = OracleHelper.GetSingle(strSql);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }

        /// <summary>
        /// 更新表字段值
        /// add:Wangjh 20181207
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableID"></param>
        /// <param name="filedName"></param>
        /// <param name="filedValue"></param>
        /// <returns></returns>
        public Boolean UpdateTableFiledValue(string table, string tableID, string filedName, string filedValue)
        {

            string strSql = string.Format(" update " + table + " t set t." + filedName + "='" + filedValue + "' where t." + table + "ID='" + tableID + "'");
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 更新表字段
        /// add：Wangjh 20221011
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableID"></param>
        /// <param name="fileds"></param>
        /// <returns></returns>
        public Boolean UpdateTableFiledsValues(string table, string tableID, Dictionary<string, string> fileds)
        {
            if (fileds.Keys.Count == 0)
            {
                return true;
            }
            string strSql = " update " + table + " t set ";

            foreach (string key in fileds.Keys)
            {
                strSql += key + "='" + fileds[key] + "' ";
            }

            strSql += " where t." + table + "ID='" + tableID + "'";
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 查询表数据根据指定的字段
        /// add:Wangjh 20181207
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filedName"></param>
        /// <param name="filedValue"></param>
        /// <returns></returns>
        public DataTable GetTableByFiledValue(string table, string filedName, string filedValue)
        {
            string strSql = string.Format("select * from " + table + " t where t." + filedName + "='" + filedValue + "'");
            return OracleHelper.Query(strSql).Tables[0];
        }

        public DataSet GetResource(string strResourceName)
        {
            string strSql = string.Format("select t.* from resourcedef t where t.resourcename='{0}'", strResourceName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }


        public DataSet GetProductByVersion(string strProductName, string strVersion)
        {
            string strSql = string.Format("select t.* from product t left join productbase pb on  pb.productbaseid=t.productbaseid " +
                                          "where  pb.productname='{0}' and t.productrevision='{1}'", strProductName, strVersion);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataTable GetRDOByAllVersion(string strTable, string strName, string strNameValue)
        {
            string strSql = string.Format("select r.*,rb.* from {0} r left join {0}base rb on r.{0}baseid=rb.{0}baseid " +
                     "  where  rb.{1}='{2}' ", strTable, strName, strNameValue);
            DataSet tmpSet = OracleHelper.Query(strSql);
            if (tmpSet == null)
                return new DataTable();
            if (tmpSet.Tables.Count == 0)
                return new DataTable();

            return tmpSet.Tables[0];
        }



        public DataSet GetBom(string bomName, string bomVersion)
        {
            string strSql = string.Format("select t.bomid,bs.bomname from bom t left join bombase bs on bs.bombaseid=t.bombaseid " +
                                          "where bs.bomname='{0}' and t.bomrevision='{1}'", bomName, bomVersion);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        //private void InsertCheckInfo(string strSpecName, string strSpecVersion, string strProduct,
        //                           string strProductVersion

        public string GetProductId(string strProduct, string strProductVersion)
        {
            string sql = string.Format(" select productid from product p inner join productbase pb ON p.productbaseid=pb.productbaseid");
            sql += string.Format(" WHERE p.productrevision='{0}' AND pb.productname='{1}'", strProductVersion, strProduct);
            object obj = OracleHelper.GetSingle(sql);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }

        }

        public string GetSpecId(string strSpecName, string strSpecVersion)
        {
            string sql = string.Format(" select specid from spec s inner join specbase sb ON s.specbaseid=sb.specbaseid");
            sql += string.Format(" WHERE s.specrevision='{0}' AND sb.specname='{1}'", strSpecVersion, strSpecName);
            object obj = OracleHelper.GetSingle(sql);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }

        }

        public DataTable GetWorkflowstepId(string strSpecName, string strItemID, string strRevision)
        {
            string sql = @"  SELECT WFS.WORKFLOWSTEPID, WFS.SPECID  FROM  WORKFLOW WF  
                     INNER JOIN WORKFLOWSTEP WFS ON WF.WORKFLOWID = WFS.WORKFLOWID";
            sql += string.Format(" WHERE WF.ITEM_ID = '{0}'  AND WFS.WORKFLOWSTEPNAME='{1}' AND WF.WORKFLOWREVISION='{2}'"
                , strItemID, strSpecName, strRevision);

            DataSet tmpSet = OracleHelper.Query(sql);
            return tmpSet.Tables[0].Copy();

        }

        public DataSet GetFactory(string factoryID)
        {
            string strSql = string.Format("select t.factoryname from factory t where t.factoryid='{0}'", factoryID);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }
        /// <summary>
        /// 插入工序工具信息
        /// </summary>
        /// <param name="list"></param>
        public void InsertWorkFlowStepTools(SortedList<string, object> list)
        {
            string strSql = @"INSERT INTO WORKFLOWSTEPTOOLS
              (FIELDID, SEQUENCE, TOOLS, WORKFLOWSTEPID, QTY)
            VALUES
              (:V_FIELDID, :V_SEQUENCE, :V_TOOLS, :V_WORKFLOWSTEPID, :V_QTY)";
            OracleParameter[] updateParam = new OracleParameter[5];

            updateParam[0] = new OracleParameter();
            updateParam[0].ParameterName = ":V_FIELDID";
            updateParam[0].OracleType = OracleType.VarChar;
            updateParam[0].Direction = ParameterDirection.Input;
            System.Threading.Thread.Sleep(1);
            updateParam[0].Value = long.Parse(DateTime.Now.ToString("yyMMddHHmmssfff"));

            updateParam[1] = new OracleParameter();
            updateParam[1].ParameterName = ":V_SEQUENCE";
            updateParam[1].OracleType = OracleType.Number;
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["sequnce"];

            updateParam[2] = new OracleParameter();
            updateParam[2].ParameterName = ":V_TOOLS";
            updateParam[2].OracleType = OracleType.VarChar;
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["toolid"];

            updateParam[3] = new OracleParameter();
            updateParam[3].ParameterName = ":V_WORKFLOWSTEPID";
            updateParam[3].OracleType = OracleType.VarChar;
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["stepid"];

            updateParam[4] = new OracleParameter();
            updateParam[4].ParameterName = ":V_QTY";
            updateParam[4].OracleType = OracleType.Number;
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = list["qty"];

            OracleHelper.ExecuteSql(strSql, updateParam);
        }
        /// <summary>
        /// 插入工步工具信息
        /// </summary>
        /// <param name="list"></param>
        public void InsertStepInfoTools(SortedList<string, object> list)
        {
            //string strSQL = string.Format("SELECT * FROM STEPINFOTOOLS WHERE STEPINFOID='{0}' AND TOOLS='{1}'", list["stepid"], list["toolid"]);


            string strSql = @"INSERT INTO STEPINFOTOOLS
              (FIELDID, SEQUENCE, TOOLS, STEPINFOID, QTY)
            VALUES
              (:V_FIELDID, :V_SEQUENCE, :V_TOOLS, :V_STEPINFOID, :V_QTY)";
            OracleParameter[] updateParam = new OracleParameter[5];

            updateParam[0] = new OracleParameter();
            updateParam[0].ParameterName = ":V_FIELDID";
            updateParam[0].OracleType = OracleType.VarChar;
            updateParam[0].Direction = ParameterDirection.Input;
            System.Threading.Thread.Sleep(1);
            updateParam[0].Value = long.Parse(DateTime.Now.ToString("yyMMddHHmmssfff"));

            updateParam[1] = new OracleParameter();
            updateParam[1].ParameterName = ":V_SEQUENCE";
            updateParam[1].OracleType = OracleType.Number;
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["sequnce"];

            updateParam[2] = new OracleParameter();
            updateParam[2].ParameterName = ":V_TOOLS";
            updateParam[2].OracleType = OracleType.VarChar;
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["toolid"];

            updateParam[3] = new OracleParameter();
            updateParam[3].ParameterName = ":V_STEPINFOID";
            updateParam[3].OracleType = OracleType.VarChar;
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["stepid"];

            updateParam[4] = new OracleParameter();
            updateParam[4].ParameterName = ":V_QTY";
            updateParam[4].OracleType = OracleType.Number;
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = list["qty"];

            OracleHelper.ExecuteSql(strSql, updateParam);
        }

        /// <summary>
        /// 保存管理工具信息,yuxj20221101移入，保存工序及工步工具信息
        /// </summary>
        /// <param name="stepinfoid"></param>
        /// <param name="i"></param>
        /// <param name="toolID"></param>
        /// <param name="qty"></param>
        public void SaveStepTool(string stepid, int i, string toolID, int qty, string type)
        {
            SortedList<string, object> list = new SortedList<string, object>();
            list.Add("stepid", stepid);
            list.Add("sequnce", i);
            list.Add("toolid", toolID);
            list.Add("qty", qty);
            string strSql = "";
            DataTable dt = new DataTable();
            if (type == "0")
            {
                //yuxj20221112增加条件，如果存在则不插入
                strSql = string.Format("select * from workflowsteptools where workflowstepid='{0}' and tools='{1}'", stepid, toolID);
                dt = OracleHelper.Query(strSql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    InsertWorkFlowStepTools(list);
                }
            }
            else if (type == "1")
            {
                strSql = string.Format("select * from stepinfotools where stepinfoid='{0}' and tools='{1}'", stepid, toolID);
                dt = OracleHelper.Query(strSql).Tables[0];
                if (dt.Rows.Count == 0)
                {
                    InsertStepInfoTools(list);
                }
            }
        }

        /// <summary>
        /// yuxj20221118检查本文件处理中在6分钟内是否出现连接camstar报错，如果是此报错，不移动文件
        /// 风险，一个文件可能在6分钟内被多次执行，不过应该影响不大
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool CheckCamstarConnectError(string fileName)
        {
            string strSql
                = string.Format(@" select il.* from interface_log il where il.logtype like 'PDMTOMES%' and 
                                   il.xml='{0}' and il.logmessage like '%{1}%' and il.createdate>sysdate-1/(240) "
                , fileName, "由于连接方在一段时间后没有正确答复");
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                //更改错误消息
                foreach (DataRow dr in dt.Rows)
                {
                    OracleHelper.ExecuteSql(string.Format("update interface_log set logmessage='{0}' where logid='{1}'",
                        "CamstarApi连接失败，等待系统重新处理xml", dr["logid"].ToString()));
                }
                return true;
            }
            else
                return false;
        }

        //此方法同时清理工序和工步工装
        public void ClearWorkFlowStepTool(string workflowstepid)
        {
            string strSql = string.Format("DELETE FROM WORKFLOWSTEPTOOLS T" +
             " WHERE  T.WORKFLOWSTEPID = '{0}' AND NOT EXISTS (SELECT 1 FROM AuxMaterial a WHERE a.auxmaterialid=t.tools)  ", workflowstepid);
            OracleHelper.ExecuteSql(strSql);
        }

        public void ClearStepInfoTool(string stepid)
        {
            string strSql = string.Format("DELETE FROM STEPINFOTOOLS T" +
             " WHERE  T.STEPINFOID = '{0}'  ", stepid);
            OracleHelper.ExecuteSql(strSql);

        }
        //清除工序关联的辅材信息
        public void ClearWorkFlowStepToolAux(string workflowstepid)
        {
            string strSql = string.Format("DELETE FROM WORKFLOWSTEPTOOLS T" +
             " WHERE  T.WORKFLOWSTEPID = '{0}'  AND EXISTS (SELECT 1 FROM AuxMaterial a WHERE a.auxmaterialid=t.tools)  ", workflowstepid);
            OracleHelper.ExecuteSql(strSql);
        }

        /// <summary>
        /// Add by Yuxj20221118 相同xml文件重复处理时先清理旧的检测项
        /// </summary>
        /// <param name="workflowstepid"></param>
        /// <param name="stepinfoid"></param>
        public void ClearStepCheckPoint(string workflowstepid, string stepinfoid)
        {
            string strSql = string.Format("DELETE FROM stepcheckpoint T" +
             " WHERE  T.WORKFLOWSTEPID = '{0}'   ", workflowstepid);
            if (string.IsNullOrEmpty(stepinfoid))
            {
                strSql += " and t.stepinfoid is null ";
            }
            else
                strSql += string.Format(" and t.stepinfoid ='{0}' ", stepinfoid);

            OracleHelper.ExecuteSql(strSql);
        }

        public bool IsExistStepCheckPoint(string productid, string workflowstepid, string name, string checkno, string checknote)
        {
            string strSql = "SELECT T.*  FROM STEPCHECKPOINT T ";

            strSql = string.Format("{0}  WHERE T.WORKFLOWSTEPID = '{1}' ", strSql, workflowstepid);


            OracleParameter[] queryParam = new OracleParameter[2];

            queryParam[0] = OracleHelper.NewOracleParameter(":TCCheckPointNote", checknote, OracleType.VarChar);
            queryParam[1] = OracleHelper.NewOracleParameter(":CHECKNO", checkno, OracleType.VarChar);
            //queryParam[2] = OracleHelper.NewOracleParameter(":TCCheckPointNote", checknote, OracleType.VarChar);
            //queryParam[3] = OracleHelper.NewOracleParameter(":TCCheckPointNote", checknote, OracleType.VarChar);
            //queryParam[4] = OracleHelper.NewOracleParameter(":TCCheckPointNote", checknote, OracleType.VarChar);


            if (!string.IsNullOrWhiteSpace(productid))
                strSql = string.Format("{0}  AND T.PRODUCTID = '{1}' ", strSql, productid);

            strSql += "  AND T.CHECKNO =:CHECKNO AND T.TCCheckPointNote=:TCCheckPointNote  ";

            if (!string.IsNullOrWhiteSpace(name))
                strSql = string.Format("{0}  AND T.NAME = '{1}' ", strSql, name);

            var re = OracleHelper.GetSingle(strSql, queryParam);

            if (re == null)
                return false;
            else
                return true;

            //DataSet tmpSet = OracleHelper.Query(strSql);
            //if (tmpSet != null && tmpSet.Tables[0].Rows.Count > 0)
            //{
            //    return true;
            //}
            //else
            //    return false;
        }

        public bool IsExistStepCheckPoint2(string productid, string workflowstepid, string stepinfoid, string name, string checkno,
            string tcDrawingRequire, string BubbleNumber, string checktype, string checkinputtype, string KeyCheck, string qualitycheck, string tcCheckPointNote, ref string id)
        {
            string strSql = string.Format("SELECT T.stepcheckpointid  FROM STEPCHECKPOINT T " +
                " WHERE  T.WORKFLOWSTEPID = '{0}' ",
                 workflowstepid);
            /*
             * -----------------------------新增去重条件 add by zhangrj 20250106--------------------------
             * -----------------------------考虑为空情况 update:wangjh 20250401--------------------------
             */
            strSql += " and T.TCDRAWINGREQUIRE " + (string.IsNullOrWhiteSpace(tcDrawingRequire) ? " is null" : "=:TCDRAWINGREQUIRE");
            strSql += " and T.tccheckpointnote " + (string.IsNullOrWhiteSpace(tcCheckPointNote) ? " is null" : "=:tccheckpointnote");

            List<OracleParameter> queryParamList = new List<OracleParameter>();
            if (!string.IsNullOrWhiteSpace(tcDrawingRequire))
            {
                queryParamList.Add(OracleHelper.NewOracleParameter(":TCDRAWINGREQUIRE", tcDrawingRequire, OracleType.VarChar));
            }
            if (!string.IsNullOrWhiteSpace(tcCheckPointNote))
            {
                queryParamList.Add(OracleHelper.NewOracleParameter(":tccheckpointnote", tcCheckPointNote, OracleType.VarChar));
            } 

            if (!string.IsNullOrWhiteSpace(checktype))
            {
                strSql += string.Format(" and T.checktype = '{0}' ", checktype);
            }

            strSql += string.Format(" and T.checkinputtype = '{0}' ", string.IsNullOrWhiteSpace(checkinputtype) ? "0" : checkinputtype);

            strSql += string.Format(" and T.keycheck = '{0}' ", string.IsNullOrWhiteSpace(KeyCheck) ? "0" : KeyCheck);

            strSql += string.Format(" and T.qualitycheck = '{0}' ", string.IsNullOrWhiteSpace(qualitycheck) ? "0" : qualitycheck);
            /*
             * -------------------------------------end--------------------------------------------------
             */

            if (!string.IsNullOrWhiteSpace(stepinfoid))
            {
                strSql += string.Format(" and T.STEPINFOID = '{0}' ", stepinfoid);
            }

            strSql += string.Format(" and T.CHECKNO='{0}' ", string.IsNullOrWhiteSpace(checkno) ? "0" : checkno);//edit by zhangrj 20250106 增加为空的判断

            if (!string.IsNullOrWhiteSpace(name))
            {
                strSql += string.Format(" and T.NAME='{0}' ", name);
            }
            else
            {
                strSql += " and T.NAME is null ";
            }

            if (!string.IsNullOrWhiteSpace(productid))
            {
                strSql += string.Format(" and T.productid='{0}' ", productid);
            }

            var re = OracleHelper.GetSingle(strSql, queryParamList.ToArray()); 
            //var re = OracleHelper.GetSingle(strSql);

            if (re != null)
            {
                id = re.ToString();
                return true;
            }
            else
            {
                //由于tc更换了关键特性标识字段,需临时处理已存在的记录,修正气泡号和关键特性标识,checkname->symbol add:Wangjh 2023/1/9
                //strSql = strSql.Replace(string.Format(" and t.NAME='{0}' ", name), "");
                //strSql = strSql.Replace(" and t.NAME is null ", "");
                //DataTable tempDt = OracleHelper.Query(strSql, queryParam).Tables[0];
                //if (tempDt.Rows.Count == 0) {
                //    return false;
                //}
                //else {
                //    foreach (DataRow dr in tempDt.Rows) {
                //        OracleHelper.ExecuteSql(string.Format(@"update stepcheckpoint st set st.name='{1}',st.bubblenumber='{2}' where st.stepcheckpointid='{0}'",
                //            dr["stepcheckpointid"].ToString(), name, BubbleNumber));
                //        id = dr["stepcheckpointid"].ToString();
                //    }
                //    return true;
                //}

                return false;
            }
        }


        public void InsertIntoStepCheckPointTable(SortedList<string, object> list)
        {
            string strSql = @"INSERT INTO STEPCHECKPOINT
                                  (CHECKINPUTTYPE,   CHECKNO,   CHECKPOINTNOTE,
                                   CHECKTYPE,   INPUTNUM,   INTIME,KEYCHECK,   MEOPCHECK,   NAME,
                                   PRODUCTID,   QUALITYCHECK,   STEPCHECKPOINTID,TCCHECKPOINTNOTE,
                                      WORKFLOWSTEPID,DRAWINGREQUIRE,STEPINFOID,ASSEMBLYTYPE,BASEVALUE,CEILFLOAT,FLOORFLOAT,BubbleNumber,IsSPC,IsInstall,IsDisassemble,IsNotice,IsReturn,IsCompp,CheckMethod,TCDrawingRequire,CheckRatio,SingLesign,CheckRequire)
                                VALUES
                                  (:V_CHECKINPUTTYPE,   :V_CHECKNO,   :V_CHECKPOINTNOTE,
                                   :V_CHECKTYPE, :V_INPUTNUM,sysdate, :V_KEYCHECK,:V_MEOPCHECK,:V_NAME,  
                                   :V_PRODUCTID,   :V_QUALITYCHECK,:V_STEPCHECKPOINTID, 
                                   :V_TCCHECKPOINTNOTE,   :V_WORKFLOWSTEPID,:V_DRAWINGREQUIRE,:V_STEPINFOID,:V_ASSEMBLYTYPE,
                                    :V_BASEVALUE, :V_CEILFLOAT, :V_FLOORFLOAT,:BubbleNumber,:IsSPC,:IsInstall,:IsDisassemble,:IsNotice,:IsReturn,:IsCompp,:CheckMethod,:TCDrawingRequire,:CheckRatio,:SingLesign,:CheckRequire)";

            if (!string.IsNullOrWhiteSpace(list["StepCheckPointId"].ToString()))//存在
            {
                strSql = @"update STEPCHECKPOINT set CHECKINPUTTYPE=:V_CHECKINPUTTYPE,CHECKNO=:V_CHECKNO,CHECKPOINTNOTE=:V_CHECKPOINTNOTE,CHECKTYPE=:V_CHECKTYPE,
                         INPUTNUM=:V_INPUTNUM,INTIME=sysdate,KEYCHECK=:V_KEYCHECK,MEOPCHECK=:V_MEOPCHECK,NAME=:V_NAME,PRODUCTID=:V_PRODUCTID,QUALITYCHECK=:V_QUALITYCHECK,
                         TCCHECKPOINTNOTE=:V_TCCHECKPOINTNOTE,WORKFLOWSTEPID=:V_WORKFLOWSTEPID,DRAWINGREQUIRE=:V_DRAWINGREQUIRE,STEPINFOID=:V_STEPINFOID,ASSEMBLYTYPE=:V_ASSEMBLYTYPE,
                         BASEVALUE=:V_BASEVALUE,CEILFLOAT=:V_CEILFLOAT,FLOORFLOAT=:V_FLOORFLOAT,BubbleNumber=:BubbleNumber,IsSPC=:IsSPC,IsInstall=:IsInstall,IsDisassemble=:IsDisassemble,
                         IsNotice=:IsNotice,IsReturn=:IsReturn,IsCompp=:IsCompp,CheckMethod=:CheckMethod,TCDrawingRequire=:TCDrawingRequire,CheckRatio=:CheckRatio,SingLesign=:SingLesign,CheckRequire=:CheckRequire where STEPCHECKPOINTID=:V_STEPCHECKPOINTID ";
            }

            OracleParameter[] updateParam = new OracleParameter[31];

            updateParam[0] = OracleHelper.NewOracleParameter(":V_CHECKINPUTTYPE", list["CheckInputType"], OracleType.Number);
            updateParam[1] = OracleHelper.NewOracleParameter(":V_CHECKNO", list["CheckNO"], OracleType.Number);
            updateParam[2] = OracleHelper.NewOracleParameter(":V_CHECKPOINTNOTE", list["CheckPointNote"], OracleType.VarChar);
            updateParam[3] = OracleHelper.NewOracleParameter(":V_CHECKTYPE", list["CheckType"], OracleType.VarChar);
            updateParam[4] = OracleHelper.NewOracleParameter(":V_INPUTNUM", list["IntNum"], OracleType.Number);
            updateParam[5] = OracleHelper.NewOracleParameter(":V_KEYCHECK", list["KeyCheck"], OracleType.Number);
            updateParam[6] = OracleHelper.NewOracleParameter(":V_MEOPCHECK", list["MeopCheck"], OracleType.Number);
            updateParam[7] = OracleHelper.NewOracleParameter(":V_NAME", list["strSymbol"], OracleType.VarChar);
            updateParam[8] = OracleHelper.NewOracleParameter(":V_PRODUCTID", list["Productid"], OracleType.VarChar);
            updateParam[9] = OracleHelper.NewOracleParameter(":V_QUALITYCHECK", list["QualityCheck"], OracleType.Number);
            if (!string.IsNullOrWhiteSpace(list["StepCheckPointId"].ToString()))//存在
            {
                updateParam[10] = OracleHelper.NewOracleParameter(":V_STEPCHECKPOINTID", list["StepCheckPointId"].ToString(), OracleType.VarChar);
            }
            else
            {
                updateParam[10] = OracleHelper.NewOracleParameter(":V_STEPCHECKPOINTID", Guid.NewGuid().ToString().Trim(), OracleType.VarChar);
            }


            updateParam[11] = OracleHelper.NewOracleParameter(":V_TCCHECKPOINTNOTE", list["TCCheckPointNote"], OracleType.VarChar);
            updateParam[12] = OracleHelper.NewOracleParameter(":V_WORKFLOWSTEPID", list["Workflowstepid"], OracleType.VarChar);
            updateParam[13] = OracleHelper.NewOracleParameter(":V_DRAWINGREQUIRE", list["DrawingRequire"], OracleType.VarChar);

            if (list.ContainsKey("StepInfoID"))
                updateParam[14] = OracleHelper.NewOracleParameter(":V_STEPINFOID", list["StepInfoID"], OracleType.VarChar);
            else
                updateParam[14] = OracleHelper.NewOracleParameter(":V_STEPINFOID", "", OracleType.VarChar);
            if (list.ContainsKey("AssemblyType"))
                updateParam[15] = OracleHelper.NewOracleParameter(":V_ASSEMBLYTYPE", list["AssemblyType"], OracleType.VarChar);
            else
                updateParam[15] = OracleHelper.NewOracleParameter(":V_ASSEMBLYTYPE", "", OracleType.VarChar);
            updateParam[16] = OracleHelper.NewOracleParameter(":V_BASEVALUE", list["BaseValue"], OracleType.VarChar);//add by longb 2022-10-09
            updateParam[17] = OracleHelper.NewOracleParameter(":V_CEILFLOAT", list["CeiFloat"], OracleType.VarChar);//add by longb 2022-10-09
            updateParam[18] = OracleHelper.NewOracleParameter(":V_FLOORFLOAT", list["FloorFloat"], OracleType.VarChar);//add by longb 2022-10-09
            updateParam[19] = OracleHelper.NewOracleParameter(":BubbleNumber", list["BubbleNumber"], OracleType.VarChar);//add by wangjh 2023-1-10
            updateParam[20] = OracleHelper.NewOracleParameter(":IsSPC", list["IsSPC"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[21] = OracleHelper.NewOracleParameter(":IsInstall", list["IsInstall"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[22] = OracleHelper.NewOracleParameter(":IsDisassemble", list["IsDisassemble"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[23] = OracleHelper.NewOracleParameter(":IsNotice", list["IsNotice"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[24] = OracleHelper.NewOracleParameter(":IsReturn", list["IsReturn"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[25] = OracleHelper.NewOracleParameter(":IsCompp", list["IsCompp"], OracleType.Number);//add by wangjh 2023-3-13
            updateParam[26] = OracleHelper.NewOracleParameter(":CheckMethod", list["CheckMethod"], OracleType.VarChar);//add by longb 2023-5-6
            updateParam[27] = OracleHelper.NewOracleParameter(":TCDrawingRequire", list["TCDrawingRequire"], OracleType.VarChar);
            updateParam[28] = OracleHelper.NewOracleParameter(":CheckRatio", list["CheckRatio"], OracleType.VarChar);
            updateParam[29] = OracleHelper.NewOracleParameter(":SingLesign", list["SingLesign"], OracleType.VarChar);//add by longb 2024.04.15
            updateParam[30] = OracleHelper.NewOracleParameter(":CheckRequire", list["CheckRequire"], OracleType.VarChar);//add by longb 2024.04.15
            OracleHelper.ExecuteSql(strSql, updateParam);
        }

        public string GetBomVersion(string bomName)
        {
            string strRev = "";
            string strSql = string.Format("select bs.bomname,bs.revofrcdid from bombase bs where bs.bomname='{0}'", bomName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            if (tmpSet != null && tmpSet.Tables[0].Rows.Count > 0)
            {
                strSql = string.Format("select t.bomrevision from bom t where t.bomid='{0}'", tmpSet.Tables[0].Rows[0]["revofrcdid"].ToString().Trim());
                DataSet revSet = OracleHelper.Query(strSql);

                if (tmpSet != null && revSet.Tables[0].Rows.Count > 0)
                {
                    strRev = revSet.Tables[0].Rows[0]["bomrevision"].ToString().Trim();
                }
            }
            return strRev;
        }

        public DataSet GetBomByName(string bomName)
        {
            string strSql = string.Format("select bs.bomname,bs.revofrcdid from bombase bs where bs.bomname='{0}'", bomName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataSet GetAllMaterialstate()
        {
            string strSql = "select * from materialstate ";
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        //获取物料信息的技术条件及材料状态
        public DataSet GetMaterialInfo_Jstj_clzt(string strMaterialInfoName)
        {
            string strQuery = "SELECT mi.materialinfoname,t.technicalconditionsname" + "\n" +
                "FROM materialinfotechnicalcondition mt" + "\n" +
                "LEFT JOIN materialinfo mi ON mt.materialinfoid = mi.materialinfoid" + "\n" +
                "LEFT JOIN technicalconditions t ON t.technicalconditionsid = mt.technicalconditionsid" + "\n" +
                "WHERE mi.materialinfoname = '" + strMaterialInfoName + "'";

            DataSet dsJstj = OracleHelper.Query(strQuery);

            strQuery = "SELECT mi.materialinfoname,m.materialstatename" + "\n" +
                "FROM materialinfomaterialstate mm" + "\n" +
                "LEFT JOIN materialinfo mi ON mm.materialinfoid = mi.materialinfoid" + "\n" +
                "LEFT JOIN materialstate m ON m.materialstateid = mm.materialstateid" + "\n" +
                "WHERE mi.materialinfoname = '" + strMaterialInfoName + "'";

            DataSet dsClzt = OracleHelper.Query(strQuery);

            DataSet ds = new DataSet();
            ds.Tables.Add(dsJstj.Tables[0].Copy());
            ds.Tables[0].TableName = "dtJstj";
            ds.Tables.Add(dsClzt.Tables[0].Copy());
            ds.Tables[1].TableName = "dtClzt";

            return ds;
        }
        public DataSet GetAllTechnicalConditions()
        {
            string strSql = "select * from TechnicalConditions ";
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }
        public DataSet GetAllToolType()
        {
            string strSql = "select * from tooltype ";
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }
        public string GetMAXCreateDate(string fieldname, string tablename)
        {
            string strSql = string.Format("select max({0}) from {1}", fieldname, tablename);
            object obj = OracleHelper.GetSingle(strSql);
            if (obj == null)
                return string.Empty;
            else
                return DateTime.Parse(obj.ToString()).ToString("yyyy-MM-dd HH:mm:ss");
        }
        public DataSet GetOrder(string orderName)
        {
            string strSql = string.Format("select t.mfgordername from mfgorder t where t.mfgordername='{0}'", orderName);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }

        public DataSet GetAXItemArrivalLine(string arrivlIneNO)
        {
            string strSql = string.Format("select t.materialinfoname from materialinfo t where t.materialinfoname='{0}'", arrivlIneNO);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet;
        }


        public void InsertWorkFolwInfo(string SpecNo, string SpecSeqence, string SpecName, string FeedBackFlag, string Status, string spec, string orderName)
        {
            string strSql = "delete from SAPSpecInfo where orderName='" + orderName + "'";
            OracleHelper.ExecuteSql(strSql);


            strSql = "insert into SAPSpecInfo (id, SpecNo, SpecSeqence, spec,SpecName,FeedBackFlag,Status,orderName) values " +
                            "(:id,:SpecNo,:SpecSeqence,:spec,:SpecName,:FeedBackFlag,:Status,:orderName)";

            OracleParameter[] updateParam = new OracleParameter[8];

            updateParam[0] = new OracleParameter();
            updateParam[0].ParameterName = ":id";
            updateParam[0].OracleType = OracleType.VarChar;
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = Guid.NewGuid().ToString().Trim();

            updateParam[1] = new OracleParameter();
            updateParam[1].ParameterName = ":SpecNo";
            updateParam[1].OracleType = OracleType.VarChar;
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = SpecNo;

            updateParam[2] = new OracleParameter();
            updateParam[2].ParameterName = ":SpecSeqence";
            updateParam[2].OracleType = OracleType.VarChar;
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = SpecSeqence;

            updateParam[3] = new OracleParameter();
            updateParam[3].ParameterName = ":spec";
            updateParam[3].OracleType = OracleType.VarChar;
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = spec;

            updateParam[4] = new OracleParameter();
            updateParam[4].ParameterName = ":SpecName";
            updateParam[4].OracleType = OracleType.VarChar;
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = SpecName;

            updateParam[5] = new OracleParameter();
            updateParam[5].ParameterName = ":FeedBackFlag";
            updateParam[5].OracleType = OracleType.VarChar;
            updateParam[5].Direction = ParameterDirection.Input;
            updateParam[5].Value = FeedBackFlag;

            updateParam[6] = new OracleParameter();
            updateParam[6].ParameterName = ":Status";
            updateParam[6].OracleType = OracleType.VarChar;
            updateParam[6].Direction = ParameterDirection.Input;
            updateParam[6].Value = Status;

            updateParam[7] = new OracleParameter();
            updateParam[7].ParameterName = ":orderName";
            updateParam[7].OracleType = OracleType.VarChar;
            updateParam[7].Direction = ParameterDirection.Input;
            updateParam[7].Value = orderName;

            OracleHelper.ExecuteSql(strSql, updateParam);
        }

        public bool InsertTCtoMES(SortedList<string, object> list)
        {
            string strSql = "insert into tcmtomesfile (id, projectid, filename, fileinstance, extension, createdate) values " +
                                "(:id,:projectid,:filename,:fileinstance,:extension,sysdate)";

            OracleParameter[] updateParam = new OracleParameter[5];

            updateParam[0] = new OracleParameter();
            updateParam[0].ParameterName = ":id";
            updateParam[0].OracleType = OracleType.VarChar;
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = list["id"];

            updateParam[1] = new OracleParameter();
            updateParam[1].ParameterName = ":projectid";
            updateParam[1].OracleType = OracleType.VarChar;
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["projectid"];

            updateParam[2] = new OracleParameter();
            updateParam[2].ParameterName = ":filename";
            updateParam[2].OracleType = OracleType.VarChar;
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["filename"];

            updateParam[3] = new OracleParameter();
            updateParam[3].ParameterName = ":extension";
            updateParam[3].OracleType = OracleType.VarChar;
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["extension"];

            updateParam[4] = new OracleParameter();
            updateParam[4].ParameterName = ":fileinstance";
            updateParam[4].OracleType = OracleType.Blob;
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = list["fileinstance"];

            int i = OracleHelper.ExecuteSql(strSql, updateParam);
            if (i == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        public bool InsertTCResourceToMES(string productname, string productrevision, string resourceid, string resourcename, int resourcetype, string specname, string specno, string specrevision, int qty)
        {
            string strSql = "insert into tcresourceforproduct (productname, productrevision, resourceid, resourcename, resourcetype, specname, specno, specrevision, qty) " +
                            " values (:productname, :productrevision, :resourceid, :resourcename, :resourcetype, :specname, :specno, :specrevision, :qty)";

            OracleParameter[] updateParam = new OracleParameter[9];

            updateParam[0] = new OracleParameter();
            updateParam[0].ParameterName = ":productname";
            updateParam[0].OracleType = OracleType.VarChar;
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = productname;

            updateParam[1] = new OracleParameter();
            updateParam[1].ParameterName = ":productrevision";
            updateParam[1].OracleType = OracleType.VarChar;
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = productrevision;

            updateParam[2] = new OracleParameter();
            updateParam[2].ParameterName = ":resourceid";
            updateParam[2].OracleType = OracleType.VarChar;
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = resourceid;

            updateParam[3] = new OracleParameter();
            updateParam[3].ParameterName = ":resourcename";
            updateParam[3].OracleType = OracleType.VarChar;
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = resourcename;

            updateParam[4] = new OracleParameter();
            updateParam[4].ParameterName = ":resourcetype";
            updateParam[4].OracleType = OracleType.Number;
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = resourcetype;

            updateParam[5] = new OracleParameter();
            updateParam[5].ParameterName = ":specname";
            updateParam[5].OracleType = OracleType.VarChar;
            updateParam[5].Direction = ParameterDirection.Input;
            updateParam[5].Value = specname;

            updateParam[6] = new OracleParameter();
            updateParam[6].ParameterName = ":specno";
            updateParam[6].OracleType = OracleType.VarChar;
            updateParam[6].Direction = ParameterDirection.Input;
            updateParam[6].Value = specno;

            updateParam[7] = new OracleParameter();
            updateParam[7].ParameterName = ":specrevision";
            updateParam[7].OracleType = OracleType.VarChar;
            updateParam[7].Direction = ParameterDirection.Input;
            updateParam[7].Value = specrevision;

            updateParam[8] = new OracleParameter();
            updateParam[8].ParameterName = ":qty";
            updateParam[8].OracleType = OracleType.Number;
            updateParam[8].Direction = ParameterDirection.Input;
            updateParam[8].Value = qty;

            int i = OracleHelper.ExecuteSql(strSql, updateParam);
            if (i == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        public void CheckTable()
        {

            StreamReader sr = new StreamReader("C:\\AAS.txt");
            ///StreamReader sr = new StreamReader("TestFile.txt",Encoding.GetEncoding("GB2312"))///GBK
            string line;
            while ((line = sr.ReadLine()) != null)
            {
                // textBox1 .Text +=ii.ToString ()+" -"+line.ToString()+"\r\n";
                string strTableName = line;
                System.Console.WriteLine(strTableName.Trim());
                string sql = "select * from " + strTableName.Trim();

                DataSet tmpSet = OracleHelper.Query(sql);
                foreach (DataRow row in tmpSet.Tables[0].Rows)
                {
                    for (int i = 0; i < tmpSet.Tables[0].Columns.Count; i++)
                    {
                        if (row.IsNull(i) == false)
                        {
                            if (row[i].ToString().Trim() == "00074e8000002cd8")
                            {
                                Log.WriteLog(strTableName + " " + tmpSet.Tables[0].Columns[i].ColumnName, 0, "测试");
                            }
                        }
                    }
                }
            }

            List<string> tableNameList = new List<string>();
            tableNameList.Add("actiondefinition");
            tableNameList.Add("activeusersession");
            tableNameList.Add("activity");
            tableNameList.Add("activityesighistorydetails");
            tableNameList.Add("activityeventfailures");
            tableNameList.Add("activitynewprocessobjects");
            tableNameList.Add("activityonexecuterules");
            tableNameList.Add("activityplan");
            tableNameList.Add("activityplanesighistorydetails");
            tableNameList.Add("activityplaneventlots");
            tableNameList.Add("activityplanoncompleterules");
            tableNameList.Add("activityplanonstartrules");
            tableNameList.Add("activityplanprerequisites");
            tableNameList.Add("activityprerequisites");
            tableNameList.Add("activitysaveddatapoints");
            tableNameList.Add("activitytemplate");
            tableNameList.Add("activitytemplateonexecuterules");
            tableNameList.Add("activitytemplateorganizations");
            tableNameList.Add("alarmdefinition");
            tableNameList.Add("alarmdefinitiontextvariables");
            tableNameList.Add("approvaldecision");
            tableNameList.Add("approvaldecisionlist");
            tableNameList.Add("approvalroutinginfo");
            tableNameList.Add("approvalsheet");
            tableNameList.Add("approvalsheetentry");
            tableNameList.Add("approvalsheethistory");
            tableNameList.Add("approvalsheetmap");
            tableNameList.Add("approvalsheettemplate");
            tableNameList.Add("assignedmaintreq");
            tableNameList.Add("assignmenthistory");
            tableNameList.Add("associatehistory");
            tableNameList.Add("associatehistorychildcnts");
            tableNameList.Add("attacheddocs");
            tableNameList.Add("billofprocess");
            tableNameList.Add("billofprocessbase");
            tableNameList.Add("billofprocessoverride");
            tableNameList.Add("billofprocessoverridetxnmap");
            tableNameList.Add("billtype");
            tableNameList.Add("bin");
            tableNameList.Add("bizrulehandlerparameter");
            tableNameList.Add("bizruleparameter");
            tableNameList.Add("bom");
            tableNameList.Add("cdofields");
            tableNameList.Add("cdofieldselvalmodes");
            tableNameList.Add("cdofieldusage");
            tableNameList.Add("cdousagemaskdefinition");
            tableNameList.Add("cdousage");
            tableNameList.Add("changecategoryhistory");
            tableNameList.Add("changeownerhistory");
            tableNameList.Add("changeprocesshistory");
            tableNameList.Add("changeprocesshistoryhistorydet");
            tableNameList.Add("changestatus");
            tableNameList.Add("changestatusreason");
            tableNameList.Add("chgattrhistory");
            tableNameList.Add("chgattrhistorydetails");
            tableNameList.Add("chgattrhistorydetailsnewdurlst");
            tableNameList.Add("chgattrhistorydetailsnewfixlst");
            tableNameList.Add("chgattrhistorydetailsnewfltlst");
            tableNameList.Add("chgattrhistorydetailsnewintlst");
            tableNameList.Add("chgattrhistorydetailsnewstrlst");
            tableNameList.Add("chgattrhistorydetailsnewboolst");
            tableNameList.Add("cdomapdefinition");
            tableNameList.Add("cdolabels");
            tableNameList.Add("cdoinheritmaskdef");
            tableNameList.Add("cdofieldmapdefinition");
            tableNameList.Add("cdofieldinheritmaskdef");
            tableNameList.Add("cdodefinition");
            tableNameList.Add("categorynotificationmaptypes");
            tableNameList.Add("categorynotificationmap");
            tableNameList.Add("categorymap");
            tableNameList.Add("carseverity");
            tableNameList.Add("capahistorydetailadditionalorg");
            tableNameList.Add("capahistorydetail");
            tableNameList.Add("capacustomhistorydetail");
            tableNameList.Add("capacustomdata");
            tableNameList.Add("capacurrentcrossrefs");
            tableNameList.Add("capaadditionalorganizations");
            tableNameList.Add("capa");
            tableNameList.Add("calendarshift");
            tableNameList.Add("buyreasongroup");
            tableNameList.Add("buyreason");
            tableNameList.Add("businessruleparametervalues");
            tableNameList.Add("businessrulehandlerdata");
            tableNameList.Add("businessrulehandler");
            tableNameList.Add("businessruleexecutionhistory");
            tableNameList.Add("businessruledatahandlers");
            tableNameList.Add("businessruledata");
            tableNameList.Add("businessrule");
            tableNameList.Add("bonusreasongroup");
            tableNameList.Add("bonusreason");
            tableNameList.Add("bommateriallistitem");
            tableNameList.Add("bombase");
            tableNameList.Add("chgattrhistorydetailsnewtmslst");
            tableNameList.Add("chgattrhistorydetailsoldboolst");
            tableNameList.Add("chgattrhistorydetailsolddurlst");
            tableNameList.Add("chgattrhistorydetailsoldfixlst");
            tableNameList.Add("chgattrhistorydetailsoldfltlst");
            tableNameList.Add("chgattrhistorydetailsoldintlst");
            tableNameList.Add("chgattrhistorydetailsoldstrlst");
            tableNameList.Add("chgattrhistorydetailsoldtmslst");
            tableNameList.Add("chgattrhistoryhistorydetails");
            tableNameList.Add("chgattrhistorynewobjects");
            tableNameList.Add("chgattrhistoryoldobjects");
            tableNameList.Add("chgcontainerattrhistory");
            tableNameList.Add("chgobjectlisthistorydetails");
            tableNameList.Add("chgobjlsthistorydetailsnewobjs");
            tableNameList.Add("chgobjlsthistorydetailsoldobjs");
            tableNameList.Add("ck8pitechnicsvalidate");
            tableNameList.Add("ck8pivalidate");
            tableNameList.Add("ckbd");
            tableNameList.Add("ckbfbzj");
            tableNameList.Add("ckbgd");
            tableNameList.Add("ckbgdstatus");
            tableNameList.Add("ckbhgpsld");
            tableNameList.Add("ckbhgtype");
            tableNameList.Add("ckbomtype");
            tableNameList.Add("ckccgzcld");
            tableNameList.Add("ckcjbgd");
            tableNameList.Add("ckcjbgdckjiancexiangnew");
            tableNameList.Add("ckcjbgdckwaiguanjiance");
            tableNameList.Add("ckckmc");
            tableNameList.Add("ckckry");
            tableNameList.Add("ckcontainermfgorderlist");
            tableNameList.Add("ckcpzlbzd");
            tableNameList.Add("ckcwmc");

            tableNameList.Add("ckdj");
            tableNameList.Add("ckdjckdjmx");
            tableNameList.Add("ckdjmx");
            tableNameList.Add("ckdocumenturl");
            tableNameList.Add("ckenforcereason");
            tableNameList.Add("ckfirstappraisalreport");
            tableNameList.Add("ckflmx");
            tableNameList.Add("ckflmxcklphinfo");
            tableNameList.Add("ckfxtzd");
            tableNameList.Add("ckfxtzdckljxhinfoforfxtzdnew");
            tableNameList.Add("ckgybzstatus");
            tableNameList.Add("ckgyclyj");
            tableNameList.Add("ckgzappraisalreport");
            tableNameList.Add("ckgzjdinfo");
            tableNameList.Add("ckgzjdinfonew");
            tableNameList.Add("ckgzprobation");
            tableNameList.Add("ckhgljh");
            tableNameList.Add("ckinitializationdatainfo");
            tableNameList.Add("ckjiancexiangnew");
            tableNameList.Add("ckjlbm");
            tableNameList.Add("ckjpgz");
            tableNameList.Add("ckjyyxj");
            tableNameList.Add("ckljkc");
            tableNameList.Add("ckljnjhs");
            tableNameList.Add("ckljpmccd");

            tableNameList.Add("ckljxhinfo");
            tableNameList.Add("ckljxhinfoforfxtzdnew");
            tableNameList.Add("ckljxhinfoforppd");
            tableNameList.Add("ckljxhinfoforppdnew");
            tableNameList.Add("ckljxhinfoforzljzd");
            tableNameList.Add("ckljxhinfoforzljzdnew");
            tableNameList.Add("cklld");
            tableNameList.Add("cklldcklldmx");
            tableNameList.Add("cklldmx");
            tableNameList.Add("cklphinfo");
            tableNameList.Add("cklscyd");
            tableNameList.Add("cklzjtjd");
            tableNameList.Add("ckmesgzinfofortem");
            tableNameList.Add("ckmesgzsjinfofortem");
            tableNameList.Add("ckmesinfofortem");
            tableNameList.Add("ckmfgproductsize");
            tableNameList.Add("ckneedbuy");
            tableNameList.Add("ckneworcopy");
            tableNameList.Add("ckoperstatusinfo");
            tableNameList.Add("ckoperworklineinfo");

            tableNameList.Add("ckpccjpx");
            tableNameList.Add("ckoutstoretoolseq");
            tableNameList.Add("ckpgd");
            tableNameList.Add("ckpgdstatus");
            tableNameList.Add("ckpgtype");
            tableNameList.Add("ckppd");
            tableNameList.Add("ckppdckljxhinfoforppdnew");
            tableNameList.Add("ckprodjcx");
            tableNameList.Add("ckprodjiancexiang");
            tableNameList.Add("ckprodjiancexiangckjiancexiang");
            tableNameList.Add("ckprodjiancexiangckprodjcx");
            tableNameList.Add("ckqxqr");
            tableNameList.Add("cksjjdbg");
            tableNameList.Add("ckskcxrd");
            tableNameList.Add("cktoolfqcresult");

            tableNameList.Add("cktoolfqcresultfqc_result_deta");
            tableNameList.Add("cktoolfqcsurfaceresult");
            tableNameList.Add("ckurldocument");
            tableNameList.Add("ckwaiguanjiance");
            tableNameList.Add("ckwgzx");
            tableNameList.Add("ckworktype");
            tableNameList.Add("ckwtcld");
            tableNameList.Add("ckwtcldstatus");
            tableNameList.Add("ckxpjljccb");
            tableNameList.Add("ckzgd");
            tableNameList.Add("ckzhjxpccjlb");
            tableNameList.Add("ckzhorfa");
            tableNameList.Add("ckzljzd");
            tableNameList.Add("ckzljzdckljxhinfoforzljzdnew");
            tableNameList.Add("ckzljzdstatus");
            tableNameList.Add("clfdefinition");
            tableNameList.Add("clfeventmap");
            tableNameList.Add("clfevents");
            tableNameList.Add("clffunctions");
            tableNameList.Add("clffunparm");
            tableNameList.Add("clffunparmval");
            tableNameList.Add("clftypes");
            tableNameList.Add("closequalityobjecthistory");
            tableNameList.Add("collectiongate");
            tableNameList.Add("combinehistdetchildcontainers");
            tableNameList.Add("combinehistory");
            tableNameList.Add("combinehistorydetail");
            tableNameList.Add("compdefectreasongroup");
            tableNameList.Add("completeactivityhistorydetails");
            tableNameList.Add("completeactivityhistory");
            tableNameList.Add("completemaintenancehistory");
            tableNameList.Add("completemaintenancehistoryhist");
            tableNameList.Add("completemainthistorydetails");
            tableNameList.Add("completeprocessobjecthistory");
            tableNameList.Add("completeriskassessmenthistory");
            tableNameList.Add("completionhistory");

            tableNameList.Add("componentdefecthistorydetail");
            tableNameList.Add("componentdefectreason");
            tableNameList.Add("componentdistributehistory");
            tableNameList.Add("componentissuehistory");
            tableNameList.Add("componentremovehistory");
            tableNameList.Add("computation");
            tableNameList.Add("computationhistory");
            tableNameList.Add("computationparamhistory");
            tableNameList.Add("computationparammap");
            tableNameList.Add("computationparamspec");
            tableNameList.Add("container");
            tableNameList.Add("containerattrhistorydetail");
            tableNameList.Add("containerattrmainthistory");
            tableNameList.Add("containercompletedsubtask");
            tableNameList.Add("containercompletedsubtaskri");
            tableNameList.Add("containercompletedtask");
            tableNameList.Add("containercompletedtaskri");
            tableNameList.Add("containercurrentcrossrefs");
            tableNameList.Add("containerdefecthistorydetail");
            tableNameList.Add("containerdefectreason");
            tableNameList.Add("containerdetail");
            tableNameList.Add("containerdetailri");
            tableNameList.Add("containergroup");
            tableNameList.Add("containergroupentries");
            tableNameList.Add("containergroupgroups");
            tableNameList.Add("containergrpdftforobjtypes");
            tableNameList.Add("containerlevel");
            tableNameList.Add("containerlevelchildlevels");
            tableNameList.Add("containerlevelparentlevels");
            tableNameList.Add("containermainthistdetail");
            tableNameList.Add("containermainthistory");
            tableNameList.Add("containermateriallistitem");
            tableNameList.Add("containermfgorderlist");
            tableNameList.Add("containerri");
            tableNameList.Add("containerstatuschangehistory");
            tableNameList.Add("contdefectreasongroup");
            tableNameList.Add("cppdatatypes");
            tableNameList.Add("createcapahistory");
            tableNameList.Add("createcapahistoryhistorydetail");

            tableNameList.Add("createeventhistory");
            tableNameList.Add("csi_query_log");
            tableNameList.Add("currentstatus");
            tableNameList.Add("currentstatusclearanceworkstns");
            tableNameList.Add("currentstatusri");
            tableNameList.Add("currentstatusworkflowstack");
            tableNameList.Add("customcontianerdisp");
            tableNameList.Add("customer");
            tableNameList.Add("customercontact");
            tableNameList.Add("customerdata");
            tableNameList.Add("customerhistorydetail");
            tableNameList.Add("cycletimegate");
            tableNameList.Add("datacollectiondef");
            tableNameList.Add("datacollectiondefbase");
            tableNameList.Add("datapoint");
            tableNameList.Add("datapointhistory");
            tableNameList.Add("datapointhistorydetail");
            tableNameList.Add("datapointqueryparam");
            tableNameList.Add("datatransport");
            tableNameList.Add("dbcategories");
            tableNameList.Add("dbcolumns");
            tableNameList.Add("dbcolumnusage");
            tableNameList.Add("dbdatasourcenames");
            tableNameList.Add("dbdatasourceusage");
            tableNameList.Add("dbidconfiguration");
            tableNameList.Add("dbidentifierhistory");
            tableNameList.Add("dbidentifier");
            tableNameList.Add("dbindexdefinition");
            tableNameList.Add("dbindexentries");
            tableNameList.Add("dbtabledefinition");
            tableNameList.Add("dbtabletype");
            tableNameList.Add("dbtype");
            tableNameList.Add("decisiontreehistorydetail");
            tableNameList.Add("defecthistory");
            tableNameList.Add("defecthistoryhistorydetails");
            tableNameList.Add("deletecontainer");


            tableNameList.Add("deletehistory");
            tableNameList.Add("deletehistorylimit");
            tableNameList.Add("designateprocessobjecthistory");
            tableNameList.Add("dictionary");
            tableNameList.Add("dictionarylabel");
            tableNameList.Add("disallowedtxns");
            tableNameList.Add("disassociatehistory");
            tableNameList.Add("disassociatehistorychildcnts");
            tableNameList.Add("dispatchdetail");
            tableNameList.Add("dispatchrule");
            tableNameList.Add("disposition");
            tableNameList.Add("dispositiondata");
            tableNameList.Add("dispositiondataeventfailures");
            tableNameList.Add("dispositionhistorydetail");
            tableNameList.Add("distributionarea");
            tableNameList.Add("distributionshift");
            tableNameList.Add("distributiontype");
            tableNameList.Add("distributionwarehouse");
            tableNameList.Add("docattachments");
            tableNameList.Add("docattachmentsdocuments");
            tableNameList.Add("docattachmentshistory");
            tableNameList.Add("docattachmentshistorydetails");
            tableNameList.Add("docattachmentshistoryhistoryde");
            tableNameList.Add("doccontents");
            tableNameList.Add("document");
            tableNameList.Add("documentbase");
            tableNameList.Add("documententry");
            tableNameList.Add("documentset");
            tableNameList.Add("documentviewer");
            tableNameList.Add("electronicprocedure");
            tableNameList.Add("electronicprocedurebase");
            tableNameList.Add("emaildistexternalrecipents");
            tableNameList.Add("emaildistribution");
            tableNameList.Add("emaildistributionemployee");
            tableNameList.Add("emaildistributionrolerecipient");
            tableNameList.Add("emailmessage");
            tableNameList.Add("emailqueue");
            tableNameList.Add("employee");
            tableNameList.Add("employeerole");
            tableNameList.Add("endcollaborationhistory");
            tableNameList.Add("enterprise");
            tableNameList.Add("eproceduredetail");
            tableNameList.Add("erpbom");

            tableNameList.Add("erpbombase");
            tableNameList.Add("erproute");
            tableNameList.Add("erproutebase");
            tableNameList.Add("errormsg");
            tableNameList.Add("esigcosignreason");
            tableNameList.Add("esighistorydetail");
            tableNameList.Add("esighistorysummary");
            tableNameList.Add("esigmeaning");
            tableNameList.Add("esigreqdetail");
            tableNameList.Add("esigreqtxnmap");
            tableNameList.Add("esigrequirement");
            tableNameList.Add("esigrolegroup");
            tableNameList.Add("esigrolegroupentries");
            tableNameList.Add("esigrolegroupgroups");
            tableNameList.Add("esigrolegrpdefforobjtypes");
            tableNameList.Add("event");
            tableNameList.Add("eventcheckboxfielddata");
            tableNameList.Add("eventcheckboxfieldhistdetail");
            tableNameList.Add("eventclassificationspecmap");
            tableNameList.Add("eventcomponent");
            tableNameList.Add("eventcomponenthistorydetail");
            tableNameList.Add("eventcurrentcrossrefs");
            tableNameList.Add("eventcustomdata");
            tableNameList.Add("eventcustomhistorydetail");
            tableNameList.Add("eventdata");
            tableNameList.Add("eventdatahistorydetail");
            tableNameList.Add("eventfailure");
            tableNameList.Add("eventfailurehistorydetail");
            tableNameList.Add("eventhistorydetail");
            tableNameList.Add("eventlot");
            tableNameList.Add("eventlotfailures");
            tableNameList.Add("eventlothistdetailfailuremodes");
            tableNameList.Add("eventlothistorydetail");
            tableNameList.Add("executetaskhistory");
            tableNameList.Add("executetaskhistprereqtasks");
            tableNameList.Add("explodedbom");
            tableNameList.Add("exportimportdetail");
            tableNameList.Add("exportimportheader");
            tableNameList.Add("exportimportlog");
            tableNameList.Add("exportimportqueue");
            tableNameList.Add("factory");
            tableNameList.Add("failurecausedata");
            tableNameList.Add("failuredata");
            tableNameList.Add("failuremode");
            tableNameList.Add("failuremodegroup");
            tableNameList.Add("failuremodegroupentries");
            tableNameList.Add("failuremodegroupgroups");
            tableNameList.Add("failuremodegrpdefaultforobjtyp");
            tableNameList.Add("failureplandata");
            tableNameList.Add("failureseverity");
            tableNameList.Add("fielddefinitions");
            tableNameList.Add("fqc_result_detail");
            tableNameList.Add("functiondefinition");
            tableNameList.Add("functiontypes");
            tableNameList.Add("funparmdef");
            tableNameList.Add("funparmusage");
            tableNameList.Add("gatestatus");
            tableNameList.Add("gatestatuscompletedsteps");
            tableNameList.Add("gatestatusstepsremaining");
            tableNameList.Add("histinq");
            tableNameList.Add("historycrossref");
            tableNameList.Add("historycrossrefri");
            tableNameList.Add("historymainline");
            tableNameList.Add("historymainlineadditionalesigs");
            tableNameList.Add("historymainlineauthorizations");
            tableNameList.Add("historymainlineesigsummaries");
            tableNameList.Add("historymainlinehistorydetails");
            tableNameList.Add("historymainlinelabelsummaries");
            tableNameList.Add("historymessage");
            tableNameList.Add("historyview");
            tableNameList.Add("historyviewhistviewdtls");
            tableNameList.Add("histviewdtl");
            tableNameList.Add("histviewdtluifields");
            tableNameList.Add("holdreason");
            tableNameList.Add("holdreleasehistory");
            tableNameList.Add("holdreleasehistorydetail");
            tableNameList.Add("idcontrol");
            tableNameList.Add("importcontents");
            tableNameList.Add("importheaderdata");
            tableNameList.Add("insitekeylist");
            tableNameList.Add("insitesiteinfo");
            tableNameList.Add("instanceidcount");
            tableNameList.Add("issueactualshistory");
            tableNameList.Add("issuedifferencereason");
            //issuehistorydetail, 
            //issuereason, 
            //labelcategory, 
            //labelhistorydetail, 
            //labelhistorysummary, 
            //labels, 
            //labeltag, 
            //labeltxnmap, 
            //labeltypes, 
            //localcontrol, 
            //localreworkreason, 
            //localreworkreasongroup, 
            //location, 
            //lossreason, 
            //lossreasongroup, 
            //maintenanceclass, 
            //maintenancereason, 
            //maintenancereq, 
            //maintenancereqbase, 
            //maintenancestatus, 
            //maintenancestatusri, 
            //maintreqdueemailtarget, 
            //maintreqpastdueemailtarget, 
            //maintreqpendingemailtarget, 
            //masterrecipe, 
            //masterrecipebase, 
            //masterrecipedetail, 
            //measureroom, 
            //menudefinition, 
            //menuitem, 
            //metaadmin, 
            //mfgcalendar, 
            //mfgcalendarmfgcalendarshifts, 
            //mfgorder, 
            //mfgordermateriallistitem, 
            //modelingaudittrail, 
            //modelingaudittrailheader, 
            //modelingesighistorydetail, 
            //modelingesigreq, 
            //modifyprocessobjecthistory, 
            //movehistory, 
            //moveinhistory, 
            //msmqmanagers, 
            //namedobjectgroup, 
            //namedobjectgroupentries, 
            //namedobjectgroupgroups, 
            //namedobjgrpdftforobjtypes, 
            //ncrcausecode, 
            //ncrcausecodegroup, 
            //ncrcausecodegroupentries, 
            //ncrcausecodegroupgroups, 
            //ncrcausecodegrpdefforobjtypes, 
            //ncrcomments, 
            //ncrcommentshistory, 
            //ncrcommentsri, 
            //ncrcreatehistory, 
            //ncrcreatehistoryfailurecodes, 
            //ncrcurrentcrossrefs, 
            //ncrdefecthistorydetails, 
            //ncrescalatehistory, 
            //ncrescalatehistoryhistorydetai, 
            //ncrescalatehistoryncrfailureco, 
            //ncrfailcodegrpdefforobjtypes, 
            //ncrfailurecode, 
            //ncrfailurecodegroup, 
            //ncrfailurecodegroupentries, 
            //ncrfailurecodegroupgroups, 
            //ncrfailuretype, 
            //ncrrescodegrpdefforobjtypes, 
            //ncrresolutioncode, 
            //ncrresolutioncodegroup, 
            //ncrresolutioncodegroupentries, 
            //ncrresolutioncodegroupgroups, 
            //ncrupdatehistdetfailurecodes, 
            //ncrupdatehistory, 
            //ncrupdatehistorydetail, 
            //nonconformancereport, 
            //nonconformancereportncrfailure, 
            //nonconformancereportri, 
            //notificationevent, 
            //notificationservers, 
            //notificationtarget, 
            //notifvar, 
            //numberingrule, 
            //numberingrulemap, 
            //objectvisibilities, 
            //occupation, 
            //openprocessobjecthistory, 
            //operation, 
            //operationlog, 
            //operationschedulingdetail, 
            //orderstatus, 
            //ordertype, 
            //organization, 
            //organizationqualityesigtxns, 
            //outboundxmldoc, 
            //outboundxmldocprocessing, 
            //outboundxmldocstatus, 
            //outboundxmldocstatuscode, 
            //owner, 
            //ownershiptypes, 
            //path, 
            //pathselector, 
            //persistencestore, 
            //phase, 
            //phaseesighistorydetails, 
            //phaseeventlots, 
            //phaseoncompleterules, 
            //phaseonstartrules, 
            //phaseprerequisites, 
            //phasetemplate, 
            //phasetemplateoncompleterules, 
            //phasetemplateonstartrules, 
            //phasetemplateorganizations, 
            //plantemplate, 
            //plantemplateoncompleterules, 
            //plantemplateonstartrules, 
            //plantemplateorganizations, 
            //portalmenudefinition, 
            //portalmenuitem, 
            //portalmessagecategory, 
            //printerlabeldefinition, 
            //printerlabeldefinitionbase, 
            //printlabelhistory, 
            //printqueue, 
            //prioritycode, 
            //prioritylevel, 
            //processedtxnguid, 
            //processinggate, 
            //processinggateoptionalsteps, 
            //processinggaterequiredsteps, 
            //processmodel, 
            //processmodelesighistorydetails, 
            //processmodeloncompleterules, 
            //processmodelonstartrules, 
            //processmodeltemplate, 
            //processmodeltemplatebase, 
            //processmodeltemplateoncomplete, 
            //processmodeltemplateonstartrul, 
            //processmodeltemplateorgs, 
            //product, 
            //productbase, 
            //productfamily, 
            //productionstatus, 
            //productionstatusallowedproduct, 
            //productionstatusri, 
            //productmateriallistitem, 
            //productoption, 
            //productsubstitutes, 
            //producttype, 
            //productvendoritems, 
            //qtyadjustgroup, 
            //qtyadjustreason, 
            //qtyhistory, 
            //qtyhistorydetails, 
            //qualitycrossreference, 
            //qualitycrossrefhistory, 
            //qualityreportconfig, 
            //qualityresolutioncode, 
            //querycategory, 
            //querydef, 
            //queryparms, 
            //querytext, 
            //querytypes, 
            //recipeitem, 
            //recipeitemprerequisitetasks, 
            //recipelist, 
            //recipelistbase, 
            //recipemateriallistitem, 
            //recorddecisiontreehistory, 
            //regulatoryagency, 
            //regulatoryreport, 
            //regulatoryreporthistorydetail, 
            //regulatoryreportmap, 
            //regulatoryreporttype, 
            //releasereason, 
            //removalreason, 
            //removedifferencereason, 
            //removehistorydetail, 
            //reorderprocessobjecthistdetail, 
            //reorderprocessobjecthistory, 
            //reportmappingconfig, 
            //reprintlabelhistory, 
            //reprocessactivityhistory, 
            //resolvedpermissions_temp, 
            //resourcedef, 
            //resourcedefri, 
            //resourcefamily, 
            //resourcegroup, 
            //resourcegroupdftforobjtypes, 
            //resourcegroupentries, 
            //resourcegroupgroups, 
            //resourcelogs, 
            //resourcepmstatus, 
            //resourcestatuscode, 
            //resourcestatuscodestatusreason, 
            //resourcestatushistory, 
            //resourcestatusreason, 
            //resourcethruputhistory, 
            //resourcethruputhistorydetails, 
            //restorecontainer, 
            //returnedequipmentaction, 
            //revisionedobjectgroup, 
            //revisionedobjectgroupentries, 
            //revisionedobjectgroupgroups, 
            //reworkreason, 
            //reworkreasongroup, 
            //reworkstatus, 
            //reworkstatusreentryworkflowstk, 
            //reworkstatusri, 
            //riskassessment, 
            //roledef, 
            //rolepermission, 
            //rolepermissionmodes, 
            //rollupreason, 
            //rollupreasongroup, 
            //routestep, 
            //routinghistory, 
            //salesorder, 
            //scheduledbizruleexechistory, 
            //scheduledbusinessrule, 
            //scrapreason, 
            //securityaudittrail, 
            //securityaudittrailheader, 
            //securitycacherefreshrequest, 
            //securitydescriptor, 
            //securitymaskdefinition, 
            //securitymaskdetail, 
            //securityservers, 
            //sellreason, 
            //sellreasongroup, 
            //serializationtypes, 
            //serializehistory, 
            //serializehistorydetails, 
            //sessionvalues, 
            //setup, 
            //setupbase, 
            //shift, 
            //shipmentdestgrpdefforobjtypes, 
            //shipmentdestination, 
            //shipmentdestinationgrp, 
            //shipmentdestinationgrpentries, 
            //shipmentdestinationgrpgroups, 
            //shippingreason, 
            //shippingreasongroup, 
            //signapprovalhistory, 
            //site, 
            //siteinfokeylist, 
            //sitestate, 
            //spec, 
            //specbase, 
            //specschedulingdetail, 
            //specstatus, 
            //specstatuscompletedtxns, 
            //spectxnmap, 
            //splithistory, 
            //splithistorydetails, 
            //sqldatatypes, 
            //startcontattrhistorydetail, 
            //starthistorydetail, 
            //starthistorydetailmfgorderlist, 
            //starthistorydtlworkflowstack, 
            //startreason, 
            //steppasscount, 
            //steppasscountri, 
            //stepschedulingdetail, 
            //substitutionreason, 
            //suspectdevicedata, 
            //suspectdevicehistorydetail, 
            //systemsecuritygroup, 
            //taskitem, 
            //taskitemprerequisitetask, 
            //tasklist, 
            //tasklistbase, 
            //team, 
            //teamemployee, 
            //testtable, 
            //textvariable, 
            //thruputhistory, 
            //thruputhistorydetail, 
            //timerevent, 
            //tooljudge, 
            //tool_container, 
            //tool_judge, 
            //trainingplan, 
            //trainingplandetail, 
            //trainingrecord, 
            //trainingrecordstatus, 
            //trainingreqgroupentries, 
            //trainingreqgroupgroups, 
            //trainingreqgrpdfltforobjtypes, 
            //trainingrequirement, 
            //trainingrequirementbase, 
            //trainingrequirementgroup, 
            //trainingrequirementtrainers, 
            //transactionidtable, 
            //transrevoids, 
            //triagequalityobjecthistory, 
            //triagespec, 
            //triagespecbusinessrules, 
            //triagespecdetail, 
            //txndetails, 
            //txnmap, 
            //ufcnews, 
            //ufcproblem, 
            //ufcresourcetype, 
            //ufcwarehouse, 
            //uidetails, 
            //uipageflow, 
            //uipageflowactivity, 
            //uipageflowstate, 
            //uipersonalization, 
            //uiportalprofile, 
            //uipreference, 
            //uipreferencemap, 
            //uipreffielddef, 
            //uivirpageuserpersonalizations, 
            //uivirtualpage, 
            //uom, 
            //updatecapahistory, 
            //updateeventhistory, 
            //userattribute, 
            //usercodegroup, 
            //usercodegroupentries, 
            //usercodegroupgroups, 
            //usercodegrpdefaultforobjtypes, 
            //userconstant, 
            //userlabel, 
            //userquery, 
            //userqueryparameter, 
            //userqueryuserqueryparameters, 
            //vendor, 
            //vendoritem, 
            //versioninfo, 
            //voidprocessobjecthistory, 
            //webpart, 
            //wipmsgconfig, 
            //wipmsgconfigoperationsexcluded, 
            //wipmsgconfigwipmsgcntrentities, 
            //wipmsgdefmgr, 
            //wipmsgdetails, 
            //wipmsgdetailsnotificationtrgts, 
            //wipmsghistorydetail, 
            //wipmsgkey, 
            //wipmsglabelkey, 
            //wipmsgoperationkey, 
            //workcenter, 
            //workflow, 
            //workflowbase, 
            //workflowstep, 
            //workflowsteppathselectors, 
            //xmlconnectpropagatorconfig

        }
        /// <summary>
        /// WriteBy: YangSJ 2017-8-18
        /// 获取工序ID
        /// </summary>
        /// <param name="strWorkflowName"></param>
        /// <param name="strWorkflowRev"></param>
        /// <param name="StepName"></param>
        /// <returns></returns>
        public string GetParentWorkflowStep(string strWorkflowName, string strWorkflowRev, string StepNo)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT WFS.WORKFLOWSTEPID  FROM WORKFLOWSTEP WFS");
            sb.AppendLine("LEFT JOIN WORKFLOW WF   ON WF.WORKFLOWID = WFS.WORKFLOWID");
            sb.AppendFormat("WHERE WF.PROCESS_NO = '{0}'   AND WF.WORKFLOWREVISION = '{1}'   AND WFS.WORKFLOWSTEPNAME like '{2}-%'", strWorkflowName, strWorkflowRev, StepNo);

            object obj = OracleHelper.GetSingle(sb.ToString());
            if (obj == null)
                return string.Empty;
            else
                return obj.ToString();
        }
        /// <summary>
        /// WriteBy: YangSJ 2017-7-22
        /// 获取工序ID
        /// </summary>
        /// <param name="strWorkflowName"></param>
        /// <param name="strWorkflowRev"></param>
        /// <param name="StepName"></param>
        /// <returns></returns>
        public string GetParentWorkflowStepID(string strWorkflowName, string strWorkflowRev, string StepName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT WFS.WORKFLOWSTEPID  FROM WORKFLOWSTEP WFS");
            sb.AppendLine("LEFT JOIN WORKFLOW WF    ON WF.WORKFLOWID = WFS.WORKFLOWID");
            sb.AppendFormat("WHERE WF.PROCESS_NO = '{0}'   AND WF.WORKFLOWREVISION = '{1}'   AND WFS.WORKFLOWSTEPNAME = '{2}'", strWorkflowName, strWorkflowRev, StepName);
            object obj = OracleHelper.GetSingle(sb.ToString());
            if (obj == null)
                return string.Empty;
            else
                return obj.ToString();
        }
        /// <summary>
        /// WriteBy: YangSJ 2017-7-22
        /// 更新子工艺
        /// </summary>
        /// <param name="workflowID"></param>
        /// <param name="workFlowVersion"></param>
        /// <returns></returns>
        public bool UpdateStepWorkflowInfo(string strWorkflowID, string strStepID)
        {
            string strSql = string.Format("UPDATE WORKFLOWSTEP SET SUBWORKFLOWBASEID='0000000000000000' ,SUBWORKFLOWID='{0}' WHERE WORKFLOWSTEPID='{1}'", strWorkflowID, strStepID);
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        ///  WriteBy: YangSJ 2017-10-24
        /// 获取工艺
        /// </summary>
        /// <param name="containers"></param>
        /// <returns></returns>
        public DataTable GetContainers(string containers)
        {
            string strSql = string.Format("select c.containerid,c.containername from container c where c.Togetherflag in ({0}) AND C.STATUS=1 ", containers);
            DataSet tmpSet = OracleHelper.Query(strSql);
            return tmpSet.Tables[0].Copy();
        }
        /// <summary>
        ///  WriteBy: YangSJ 2017-10-24
        /// 插入超越单
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool InsertSurpassInfo(SortedList<string, object> list)
        {

            //若系统已有相同超越单号数据,先清理,然后再创建 20190313
            string delSql = string.Format("select distinct SPBI.SURPASSBILLINFOID  from SURPASSBILLINFO SPBI where  SPBI.FILENO='{0}'", list["FileNO"].ToString());
            DataTable dt = OracleHelper.Query(delSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                delSql = "";
                foreach (DataRow dr in dt.Rows)
                {

                    //1.删除超越单号
                    delSql = string.Format("delete SURPASSBILLINFO SPBI where spbi.surpassbillinfoid='{0}'", dr["SURPASSBILLINFOID"].ToString());
                    OracleHelper.ExecuteSql(delSql);
                    //2.删除超越单明细和批次映射信息
                    delSql = string.Format("select *  from surpassbilldetail SPBI where spbi.surpassbillinfoid='{0}'", dr["SURPASSBILLINFOID"].ToString());
                    DataTable dt2 = OracleHelper.Query(delSql).Tables[0];
                    foreach (DataRow dr2 in dt2.Rows)
                    {
                        //2.1删除超越单批次映射信息
                        delSql = string.Format("delete surpassbillmapping SPBI where spbi.surpassbilldetailid='{0}'", dr2["surpassbilldetailid"].ToString());
                        OracleHelper.ExecuteSql(delSql);
                    }
                    //2.2删除超越单明细
                    delSql = string.Format("delete surpassbilldetail SPBI where spbi.surpassbillinfoid='{0}'", dr["SURPASSBILLINFOID"].ToString());
                    OracleHelper.ExecuteSql(delSql);
                    //3.删除超越单附件 
                    delSql = string.Format("select *  from surpassaccessoriespath SPBI where spbi.surpassbillinfoid='{0}'", dr["SURPASSBILLINFOID"].ToString());
                    DataTable dt3 = OracleHelper.Query(delSql).Tables[0];
                    string temDir = @"D:\Program Files (x86)\Camstar\InSite Web Application\InSiteWebApplication\Temp\";
                    foreach (DataRow dr3 in dt3.Rows)
                    {
                        string accessoriesPath = temDir + dr3["accessoriesPath"].ToString();
                        FileInfo fi = new FileInfo(accessoriesPath);
                        if (fi.Exists)
                        {
                            fi.Delete();
                        }
                    }
                    delSql = string.Format("delete surpassaccessoriespath SPBI where spbi.surpassbillinfoid='{0}'", dr["SURPASSBILLINFOID"].ToString());
                    OracleHelper.ExecuteSql(delSql);

                }

            }
            //


            string strSql = @"INSERT INTO SURPASSBILLINFO
              (SURPASSBILLINFOID, SURPASSBILLNAME, SURPASSBILLREVISION, PRODUCTNAME, PRODUCTDES,FILENO,PROCESSNO,FACTORYNAME,CONTAINERNAME,EFFECTIVE)
            VALUES
              (:V_SURPASSBILLINFOID, :V_SURPASSBILLNAME, :V_SURPASSBILLREVISION, :V_PRODUCTNAME, :V_PRODUCTDES,
              :V_FILENO,:V_PROCESSNO,:V_FACTORYNAME,:V_CONTAINERNAME,:V_EFFECTIVE)";
            OracleParameter[] updateParam = new OracleParameter[10];

            updateParam[0] = new OracleParameter(":V_SURPASSBILLINFOID", OracleType.VarChar);
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = list["SurpassBillInfoID"];

            updateParam[1] = new OracleParameter(":V_SURPASSBILLNAME", OracleType.VarChar);
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["SurpassBillInfoName"];

            updateParam[2] = new OracleParameter(":V_SURPASSBILLREVISION", OracleType.VarChar);
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["SurpassBillInfoRevision"];

            updateParam[3] = new OracleParameter(":V_PRODUCTNAME", OracleType.VarChar);
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["ProductName"];

            updateParam[4] = new OracleParameter(":V_PRODUCTDES", OracleType.VarChar);
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = list["ProductDes"];

            updateParam[5] = new OracleParameter(":V_FILENO", OracleType.VarChar);
            updateParam[5].Direction = ParameterDirection.Input;
            updateParam[5].Value = list["FileNO"];

            updateParam[6] = new OracleParameter(":V_PROCESSNO", OracleType.VarChar);
            updateParam[6].Direction = ParameterDirection.Input;
            updateParam[6].Value = list["ProcessNO"];

            updateParam[7] = new OracleParameter(":V_FACTORYNAME", OracleType.VarChar);
            updateParam[7].Direction = ParameterDirection.Input;
            updateParam[7].Value = list["FactoryName"];

            updateParam[8] = new OracleParameter(":V_CONTAINERNAME", OracleType.VarChar);
            updateParam[8].Direction = ParameterDirection.Input;
            updateParam[8].Value = list["ContainerName"];

            updateParam[9] = new OracleParameter(":V_EFFECTIVE", OracleType.VarChar);
            updateParam[9].Direction = ParameterDirection.Input;
            updateParam[9].Value = list["Effective"];

            int i = OracleHelper.ExecuteSql(strSql, updateParam);
            if (i > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        ///  WriteBy: YangSJ 2017-10-24
        /// 插入超越单明细
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool InsertSurpassDetail(SortedList<string, object> list)
        {
            string strSql = @"INSERT INTO SURPASSBILLDETAIL
              (SURPASSBILLINFOID, SURPASSBILLDETAILID, SURPASSTYPE, SURPASSOP, REMARK)
            VALUES
              (:V_SURPASSBILLINFOID, :V_SURPASSBILLDETAILID, :V_SURPASSTYPE, :V_SURPASSOP, :V_REMARK)";
            OracleParameter[] updateParam = new OracleParameter[5];

            updateParam[0] = new OracleParameter(":V_SURPASSBILLINFOID", OracleType.VarChar);
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = list["SurpassBillInfoID"];

            updateParam[1] = new OracleParameter(":V_SURPASSBILLDETAILID", OracleType.VarChar);
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["SurpassBillDetailID"];

            updateParam[2] = new OracleParameter(":V_SURPASSTYPE", OracleType.VarChar);
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["SurpassType"];

            updateParam[3] = new OracleParameter(":V_SURPASSOP", OracleType.VarChar);
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["SurpassOP"];

            updateParam[4] = new OracleParameter(":V_REMARK", OracleType.VarChar);
            updateParam[4].Direction = ParameterDirection.Input;
            updateParam[4].Value = list["Remark"];

            int i = OracleHelper.ExecuteSql(strSql, updateParam);
            if (i > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        ///  WriteBy: YangSJ 2017-10-24
        /// 插入超越单映射
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool InsertSurpassMapping(SortedList<string, object> list)
        {
            string strSql = @"INSERT INTO SURPASSBILLMAPPING
              (SURPASSBILLDETAILID, CONTAINERID, PROCESSNO, SPECNO)
            VALUES
              (:V_SURPASSBILLDETAILID, :V_CONTAINERID, :V_PROCESSNO, :V_SPECNO)";
            OracleParameter[] updateParam = new OracleParameter[4];

            updateParam[0] = new OracleParameter(":V_SURPASSBILLDETAILID", OracleType.VarChar);
            updateParam[0].Direction = ParameterDirection.Input;
            updateParam[0].Value = list["SurpassBillDetailID"];

            updateParam[1] = new OracleParameter(":V_CONTAINERID", OracleType.VarChar);
            updateParam[1].Direction = ParameterDirection.Input;
            updateParam[1].Value = list["ContainerID"];

            updateParam[2] = new OracleParameter(":V_PROCESSNO", OracleType.VarChar);
            updateParam[2].Direction = ParameterDirection.Input;
            updateParam[2].Value = list["ProcessNO"];

            updateParam[3] = new OracleParameter(":V_SPECNO", OracleType.VarChar);
            updateParam[3].Direction = ParameterDirection.Input;
            updateParam[3].Value = list["SpecNO"];

            int i = OracleHelper.ExecuteSql(strSql, updateParam);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// WriteBy: YangSJ 2017-11-6
        /// 更新商品工时
        /// </summary>
        /// <param name="workflowID"></param>
        /// <param name="workFlowVersion"></param>
        /// <returns></returns>
        public bool UpdateStepManHoursInfo(string strWorkflowStepID, decimal StepManHours)
        {
            string strSql = string.Format("UPDATE WORKFLOWSTEP SET MANHOURS={0} WHERE WORKFLOWSTEPID='{1}'", StepManHours, strWorkflowStepID);
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }
        public bool UpdateStepControlCodeInfo(string strWorkflowStepID, string ControlCode)
        {
            string strSql = string.Format("UPDATE WORKFLOWSTEP SET CONTROLCODE='{0}' WHERE WORKFLOWSTEPID='{1}'", ControlCode, strWorkflowStepID);
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 插入超越单储存路径信息
        /// add:Wangjh 20171122
        /// </summary>
        /// <param name="surpassID"></param>
        /// <param name="accessoriesPath"></param>
        /// <returns></returns>
        public bool InsertSurpassAccessories(string surpassID, string accessoriesPath)
        {
            string insertSql = @" insert into SurpassAccessoriesPath  (SurpassBillInfoID, AccessoriesPath, createdate) values (:SurpassBillInfoID, :AccessoriesPath, sysdate)";
            OracleParameter[] insertParam = new OracleParameter[2];

            insertParam[0] = new OracleParameter(":SurpassBillInfoID", OracleType.VarChar);
            insertParam[0].Direction = ParameterDirection.Input;
            insertParam[0].Value = surpassID;

            insertParam[1] = new OracleParameter(":AccessoriesPath", OracleType.VarChar);
            insertParam[1].Direction = ParameterDirection.Input;
            insertParam[1].Value = accessoriesPath;

            int i = OracleHelper.ExecuteSql(insertSql, insertParam);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 获取指定分厂指定工艺名的所有工艺规程
        /// add:Wangjh 20180201
        /// </summary>
        /// <param name="factory"></param>
        /// <param name="workflowname"></param>
        /// <returns></returns>
        public DataTable GetAllWorkflow(string factory, string workflowname)
        {
            string strSql = @"
                            select wb.workflowname,w.workflowrevision,w.workflowid from workflow w 
                            left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                            left join factory fa on fa.factoryid=w.factoryid
                            ";
            strSql += string.Format("where fa.factoryname ='{0}' and wb.workflowname='{1}'", factory, workflowname);
            strSql += " order by w.workflowid desc";
            return OracleHelper.Query(strSql).Tables[0].Copy();
        }

        /// <summary>
        /// 获取指定工艺工序工时
        /// add:Wangjh 20180201
        /// </summary>
        /// <param name="workflowID"></param>
        /// <returns></returns>
        public DataTable GetWorkflowStepTime(string workflowID)
        {
            string strSql = @"
                            SELECT WFS.WORKFLOWSTEPNAME,WFS.NOTES,STD.DURATIONPERUNIT,WFS.WORKFLOWSTEPID,WFS.SEQUENCE,WFS.WORKCENTERID,WC.WORKCENTERNAME,WFS.TEAMID,T.TEAMNAME, nvl(S.SPECNO,substr(wfs.workflowstepname,0,(instr(wfs.workflowstepname,'-')-1))) SPECNO,
                            WFS.manhours,wfs.makecycle,wfs.preparecycle,wfs.shipcycle,wfs.commicycle,wfs.transfercycle,wfs.chargingqty,wfs.makeempcounts,wfs.assemblycycle,wfs.ParallelGroupName,wfs.Isbottleneck,wfs.WorkStationIDs,wfs.MultimachineParallel,s.specid,
                            wfs.wipmsglabel
                            FROM WORKFLOWSTEP WFS
                            LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = WFS.WORKCENTERID
                            LEFT JOIN TEAM T ON T.TEAMID = WFS.TEAMID
                            LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = WFS.WORKFLOWSTEPID
                            LEFT JOIN SPECBASE SB ON SB.SPECBASEID=WFS.SPECBASEID
                            LEFT JOIN SPEC S ON S.SPECID=NVL(SB.REVOFRCDID,WFS.SPECID)
                            ";
            strSql += string.Format(" WHERE WFS.WORKFLOWID = '{0}'", workflowID);
            strSql += " ORDER BY WFS.SEQUENCE asc";
            return OracleHelper.Query(strSql).Tables[0].Copy();
        }

        /// <summary>
        /// 更新工时,商品工时，班组和工区
        /// add:Wangjh 20180201
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public Boolean UpdateStepTimeInfo(DataRow dr)
        {
            string strSQL = "";

            strSQL = String.Format("UPDATE stepschedulingdetail set durationperunit='{0}' where stepid='{1}'",
                                      dr["durationperunit"], dr["workflowstepid"]);
            int i = OracleHelper.ExecuteSql(strSQL);
            if (i < 0)
                return false;

            strSQL = String.Format(@"UPDATE Workflowstep wfs SET wfs.notes='{0}',wfs.workcenterid='{1}',wfs.teamid='{2}',WFS.manhours='{3}',WFS.makecycle='{4}',wfs.preparecycle='{5}', 
            wfs.shipcycle='{6}',wfs.commicycle='{7}',wfs.transfercycle='{8}',wfs.chargingqty='{9}',wfs.assemblycycle='{10}',wfs.makeempcounts='{11}',
            wfs.ParallelGroupName='{12}',wfs.Isbottleneck='{13}',wfs.WorkStationIDs='{14}',wfs.MultimachineParallel='{15}' WHERE wfs.workflowstepid='{16}'",
            dr["notes"], dr["workCenterID"], dr["teamID"], dr["manhours"], dr["makecycle"].ToString(), dr["preparecycle"].ToString(), dr["shipcycle"].ToString(), dr["commicycle"].ToString(),
            dr["transfercycle"].ToString(), dr["chargingqty"].ToString(), dr["assemblycycle"].ToString(), dr["makeempcounts"].ToString(), dr["ParallelGroupName"].ToString(),
            dr["Isbottleneck"].ToString(), dr["WorkStationIDs"].ToString(), dr["MultimachineParallel"].ToString(), dr["workflowstepid"]);
            i = OracleHelper.ExecuteSql(strSQL);
            if (i > 0)
                return true;
            else
                return false;

        }
        /// <summary>
        /// 继承工序设备数据 add by longb 2023-07-21
        /// </summary>
        /// <param name="lastSpecid">上一版工艺工序id</param>
        /// <param name="newSpecid">新版工艺工序id</param>
        public void CreateStepResourceData(string lastSpecid, string newSpecid)
        {
            string sql = string.Format("select * from specresourceinfo sr where sr.specid='{0}'", lastSpecid);
            DataTable lastdt = OracleHelper.Query(sql).Tables[0];
            if (lastdt.Rows.Count > 0)
            {
                var sqlList = new ArrayList();
                foreach (DataRow row in lastdt.Rows)
                {
                    string strsql = string.Format("select * from specresourceinfo sr where sr.specid='{0}' and sr.resourceid='{1}'", newSpecid, row["resourceid"].ToString());
                    DataTable dt = OracleHelper.Query(strsql).Tables[0];
                    if (dt.Rows.Count == 0)
                    {
                        sqlList.Add(string.Format("insert INTO specresourceinfo (specresourceinfoid,specid,resourceid) values(sys_guid(),'{0}','{1}')", newSpecid, row["resourceid"].ToString()));
                    }
                }
                if (sqlList.Count > 0)
                {
                    OracleHelper.ExecuteSqlTran(sqlList);
                }
            }
        }

        /// <summary>
        /// 查询空子工艺临时
        /// add:Wangjh 2018/10/9
        /// </summary>
        /// <returns></returns>
        public DataTable GetSubWorkflowEmpty()
        {
            string strSql = @"SELECT wb.workflowname,w.workflowrevision,ut.attributevalue FROM workflow w
                    LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                    LEFT JOIN factory fa ON fa.factoryid=w.factoryid
                    LEFT JOIN Userattribute ut ON ut.parentid=fa.factoryid AND ut.userattributename='分厂代码'
                    WHERE wb.workflowname='空子工艺临时'";
            return OracleHelper.Query(strSql).Tables[0];

        }

        /// <summary>
        /// 获取工序子工艺
        /// </summary>
        /// <returns></returns>
        public DataTable GetStepSubWorkflow(string workflowname, string workflowstepname)
        {
            string strSQL = @"select wfb.workflowname,wfs.workflowstepname,swfb.workflowname sname ,swf.workflowrevision srev
                                from workflowbase wfb 
                                left join workflowstep wfs on wfb.revofrcdid=wfs.workflowid
                                left join workflow swf on wfs.subworkflowid=swf.workflowid
                                left join workflowbase swfb on swf.workflowbaseid=swfb.workflowbaseid ";
            strSQL += string.Format(" where wfb.workflowname='{0}' and wfs.workflowstepname='{1}'", workflowname, workflowstepname);
            return OracleHelper.Query(strSQL).Tables[0].Copy();
        }
        /// <summary>
        /// 件号工艺映射yangsj 2018-4-13
        /// </summary>
        /// <param name="strWFName"></param>
        /// <param name="strWFRev"></param>
        /// <param name="strPName"></param>
        /// <param name="strPRev"></param>
        /// <returns></returns>
        public bool SaveProductWorkflowMapping(string strWFName, string strWFRev, string strPName, string strPRev, bool isSubworkflow = false)
        {
            string strSql = "";
            //Yuxj20200703取消
            //            string strSql = @"select t.workflowid,t.productid from ProductWorkflowMapping t 
            //                            left join product p on t.productid=p.productid 
            //                            left join workflow wf on wf.workflowid=t.workflowid 
            //                            where p.productid is null  or wf.workflowid is null";
            //            DataSet tmpMappingset = OracleHelper.Query(strSql);
            //            if (tmpMappingset.Tables.Count > 0)
            //            {
            //                foreach (DataRow dr in tmpMappingset.Tables[0].Rows)
            //                {
            //                    strSql = string.Format("delete from ProductWorkflowMapping where ProductID='{0}' and WorkflowID='{1}'", dr["ProductID"], dr["WorkflowID"]);
            //                    OracleHelper.ExecuteSql(strSql);
            //                }
            //            }

            string ProductID = GetRDOID("Product", "ProductName", strPName, "ProductRevision", strPRev);
            string WorkflowID = GetRDOID("Workflow", "WorkflowName", strWFName, "WorkflowRevision", strWFRev);
            if (string.IsNullOrWhiteSpace(ProductID) || string.IsNullOrWhiteSpace(WorkflowID))
                return false;
            if (isSubworkflow) //子工艺情况下，更新一下workflow.product，大多数时候是先把子工艺错挂到主件号下了，然后再挂到子件号，所以此时需要更新 add:Wangjh 20220517
            {
                strSql = "update workflow w SET w.productid='{0}' WHERE w.workflowid='{1}'";
                OracleHelper.ExecuteSql(string.Format(strSql, ProductID, WorkflowID));
            }
            strSql = string.Format("select * from ProductWorkflowMapping where ProductID='{0}' and WorkflowID='{1}'", ProductID, WorkflowID);
            DataSet tmpset = OracleHelper.Query(strSql);
            if (tmpset.Tables.Count > 0 && tmpset.Tables[0].Rows.Count > 0)
                return true;

            strSql = @" insert into ProductWorkflowMapping  (ProductID, WorkflowID) values (:ProductID, :WorkflowID)";
            OracleParameter[] insertParam = new OracleParameter[2];

            insertParam[0] = OracleHelper.NewOracleParameter(":ProductID", ProductID, OracleType.VarChar);
            insertParam[1] = OracleHelper.NewOracleParameter(":WorkflowID", WorkflowID, OracleType.VarChar);

            int i = OracleHelper.ExecuteSql(strSql, insertParam);
            if (i > 0)
                return true;
            else
                return false;
        }

        public bool SaveProductSubProdcut(Dictionary<string, string> para)
        {
            string strSql = "";
            OracleParameter[] insertParam = null;
            strSql = @"INSERT INTO PRODUCTSUBPRODUCT
                     (PRODUCTID, SUBPRODUCTID)
                        VALUES
                    (:PRODUCTID,:SUBPRODUCTID)";
            string SubProductID = GetRDOID("Product", "ProductName", para["SubProduct"], "ProductRevision", para["SubProductRev"]);
            string ProductID = GetRDOID("Product", "ProductName", para["Product"], "ProductRevision", para["ProductRev"]);

            string checkSql = string.Format("select * from productsubproduct pp WHERE pp.productid='{0}' AND pp.subproductid='{1}'",
                                            ProductID, SubProductID);
            if (OracleHelper.Query(checkSql).Tables[0].Rows.Count > 0)
            {
                return true;
            }
            insertParam = new OracleParameter[2];
            insertParam[0] = OracleHelper.NewOracleParameter(":PRODUCTID", ProductID, OracleType.VarChar);
            insertParam[1] = OracleHelper.NewOracleParameter(":SUBPRODUCTID", SubProductID, OracleType.VarChar);
            int i = OracleHelper.ExecuteSql(strSql, insertParam);
            if (i > 0)
                return true;
            else
                return false;

            //            if (para.ContainsKey("SubWorkflow"))
            //            {
            //                string FactoryID = GetNDOID("Factory", "FactoryName", para["Factory"]);
            //                string SubProductID = GetRDOID("Product", "ProductName", para["SubProduct"], "ProductRevision", para["SubProductRev"]);
            //                string SubWorkflowID = GetRDOID("Workflow", "WorkflowName", para["SubWorkflow"], "WorkflowRevision", para["SubWorkflowRev"]);
            //                strSql = @"UPDATE PRODUCTSUBPRODUCT
            //                       SET FACTORYID     = :FACTORYID,
            //                        SUBWORKFLOWID = :SUBWORKFLOWID
            //                     WHERE SUBPRODUCTID  = :SUBPRODUCTID";
            //                insertParam = new OracleParameter[3];

            //                insertParam[0] = OracleHelper.NewOracleParameter(":FACTORYID", FactoryID, OracleType.VarChar);
            //                insertParam[1] = OracleHelper.NewOracleParameter(":SUBWORKFLOWID", SubWorkflowID, OracleType.VarChar);
            //                insertParam[2] = OracleHelper.NewOracleParameter(":SUBPRODUCTID", SubProductID, OracleType.VarChar);


            //            }
            //            else
            //            {
            //                string ProductID = GetRDOID("Product", "ProductName", para["Product"], "ProductRevision", para["ProductRev"]);
            //                string SubProductID = GetRDOID("Product", "ProductName", para["SubProduct"], "ProductRevision", para["SubProductRev"]);
            //                string WorkflowID = GetRDOID("Workflow", "WorkflowName", para["Workflow"], "WorkflowRevision", para["WorkflowRev"]);
            //                strSql = @"INSERT INTO PRODUCTSUBPRODUCT
            //                  (PRODUCTID, SUBPRODUCTID,  WORKFLOWID)
            //                VALUES
            //                 (:PRODUCTID,:SUBPRODUCTID, :WORKFLOWID)";
            //                insertParam = new OracleParameter[3];

            //                insertParam[0] = OracleHelper.NewOracleParameter(":PRODUCTID", ProductID, OracleType.VarChar);
            //                insertParam[1] = OracleHelper.NewOracleParameter(":SUBPRODUCTID", SubProductID, OracleType.VarChar);
            //                insertParam[2] = OracleHelper.NewOracleParameter(":WORKFLOWID", WorkflowID, OracleType.VarChar);
            //            }

        }

        /// <summary>
        /// Add by Yuxj20181018
        /// 判断集件/去封 工序有没有添加对应工步
        /// </summary>
        /// <param name="StepId"></param>
        /// <param name="StepDesc"></param>
        /// <returns></returns>
        public bool StepInfoExists(string StepId, string StepDesc)
        {
            string Strsql = string.Format(" select * from stepinfo si where si.workflowstepid='{0}' and si.description='{1}' ", StepId, StepDesc);
            DataSet tmpset = OracleHelper.Query(Strsql);
            if (tmpset.Tables.Count > 0 && tmpset.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
                return false;


        }

        /// <summary>
        /// 插入对象映射信息
        /// add:Wangjh 20181114
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public bool InsertUrl(Dictionary<string, string> para)
        {
            //重复控制
            string strSql = string.Format(@"select *  from pdmurlrecord purl where purl.filename='{0}' and purl.fileurllink='{1}' and purl.parentid='{2}'", para["FileName"], para["Link"], para["ParentID"]);
            if (OracleHelper.Query(strSql).Tables[0].Rows.Count > 0)//已存在
                return false;
            //
            string inertSql = @"INSERT INTO pdmurlrecord purl (purl.filename,purl.fileurllink,purl.parentid)VALUES(:filename,:fileurllink,:parentid)";
            OracleParameter[] insertParam = new OracleParameter[3];

            insertParam[0] = new OracleParameter("filename", para["FileName"]);
            insertParam[0].Direction = ParameterDirection.Input;
            insertParam[0].OracleType = OracleType.VarChar;

            insertParam[1] = new OracleParameter("fileurllink", para["Link"]);
            insertParam[1].Direction = ParameterDirection.Input;
            insertParam[1].OracleType = OracleType.VarChar;

            insertParam[2] = new OracleParameter("parentid", para["ParentID"]);
            insertParam[2].Direction = ParameterDirection.Input;
            insertParam[2].OracleType = OracleType.VarChar;
            int i = OracleHelper.ExecuteSql(inertSql, insertParam);
            if (i <= 0)
                return false;
            else
                return true;

        }

        /// <summary>
        /// 获取装试工序ID
        /// add:Wangjh 20181114
        /// </summary>
        /// <param name="workflowstepID"></param>
        /// <returns></returns>
        public string GetStepInfoIDByStepNameAndRev(string stepName, string revision)
        {
            string strSql = string.Format(@"SELECT st.stepinfoid  FROM stepinfo st
                                        LEFT JOIN stepinfobase sb ON sb.stepinfobaseid=st.stepinfobaseid
                                        WHERE sb.stepinfoname='{0}' AND st.revision='{1}'", stepName, revision);
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["stepinfoid"].ToString();
            }
            else
            {
                return "";
            }

        }

        /// <summary>
        /// 保存主子工序映射表
        /// add:Wangjh 20181207
        /// </summary>
        /// <param name="workflowID"></param>
        /// <param name="sonWorkflowID"></param>
        /// <param name="workflowstepID"></param>
        /// <param name="sonWorkflowStepID"></param>
        /// <returns></returns>
        public Boolean SaveStepSubStep(string workflowID, string sonWorkflowID, string workflowstepID, string sonWorkflowStepID)
        {
            string strSql = string.Format(@" delete FROM stepsubstep sus WHERE sus.workflowstepid='{0}' and (sus.sonworkflowid is null or sus.sonworkflowid!='{1}' )",
                workflowstepID, sonWorkflowID);
            OracleHelper.ExecuteSql(strSql);

            if (OracleHelper.GetSingle(string.Format("select 1 from stepsubstep where workflowstepid='{0}' and sonworkflowstepid ='{1}'", workflowstepID, sonWorkflowStepID)) != null)
            {
                return true;
            }

            strSql = @"INSERT INTO stepsubstep sus (sus.workflowstepid,sus.sonworkflowstepid,sus.workflowid,sus.sonworkflowid)
                    VALUES(:workflowstepid,:sonworkflowstepid,:workflowid,:sonworkflowid)";
            OracleParameter[] insertParam = new OracleParameter[4];

            insertParam[0] = new OracleParameter("workflowstepid", workflowstepID);
            insertParam[0].Direction = ParameterDirection.Input;
            insertParam[0].OracleType = OracleType.VarChar;

            insertParam[1] = new OracleParameter("sonworkflowstepid", sonWorkflowStepID);
            insertParam[1].Direction = ParameterDirection.Input;
            insertParam[1].OracleType = OracleType.VarChar;

            insertParam[2] = new OracleParameter("workflowid", workflowID);
            insertParam[2].Direction = ParameterDirection.Input;
            insertParam[2].OracleType = OracleType.VarChar;

            insertParam[3] = new OracleParameter("sonworkflowid", sonWorkflowID);
            insertParam[3].Direction = ParameterDirection.Input;
            insertParam[3].OracleType = OracleType.VarChar;

            int i = OracleHelper.ExecuteSql(strSql, insertParam);
            if (i > 0)
                return true;
            else
                return false;
        }

        //继承上一版本bom信息 add zhunan 2021.10.9
        public void inheritPerVersionBomProductInfo(string oldBomid, string newBomName, string newRev)
        {
            if (oldBomid != "" && newBomName != "" && newRev != "")
            {
                string sql = @"
SELECT pm.productmateriallistitemid,pm.bomid,pm.productid,t.yjwjh,t.zynr,t.xzsy,t.print_status
FROM productmateriallistitem pm
LEFT JOIN bom b ON b.bomid = pm.bomid
LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
LEFT JOIN product p ON p.productid = pm.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN 
(
  --上版本
  SELECT bb.bomname,pb.productname,pm.yjwjh,pm.zynr,pm.xzsy,pm.print_status
  FROM productmateriallistitem pm
  LEFT JOIN bom b ON b.bomid = pm.bomid
  LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
  LEFT JOIN product p ON p.productid = pm.productid
  LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
  WHERE pm.print_status IS NOT NULL ";

                sql += string.Format(" AND pm.bomid = '{0}' ", oldBomid);
                sql += @"
) t ON t.bomname = bb.bomname  ";

                sql += string.Format(" WHERE t.productname = pb.productname and bb.bomname = '{0}' and b.bomrevision='{1}' ", newBomName, newRev);

                DataSet tmpSet = OracleHelper.Query(sql);
                if (tmpSet.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in tmpSet.Tables[0].Rows)
                    {
                        string updatesql = string.Format("UPDATE productmateriallistitem pm SET pm.yjwjh = '{0}',pm.zynr = '{1}',pm.xzsy = '{2}',pm.print_status = '{3}' WHERE pm.productmateriallistitemid = '{4}'",
                            dr["yjwjh"].ToString(), dr["zynr"].ToString(), dr["xzsy"].ToString(), dr["print_status"].ToString(), dr["productmateriallistitemid"].ToString());
                        int i = OracleHelper.ExecuteSql(updatesql);
                    }
                }
            }

        }


        /// <summary>
        /// 608锻件情况，接收erp订单时创建的件号
        /// </summary>
        /// <param name="name"></param>
        /// <param name="rev"></param>
        /// <returns></returns>
        public DataTable GetMDJERPProduct(string name)
        {
            string strSql = @" select pb.productname,p.productrevision,pb.Revofrcdid,p.productid from product p
                               left join productbase pb on pb.productbaseid=p.productbaseid
                                 LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid
                               where p.materialcode='" + name + "' and p.materialcode<>pb.productname AND pt.producttypename='叶片锻件虚拟件'";

            return OracleHelper.Query(strSql).Tables[0];

        }

        /// <summary>
        /// 获取文档信息
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable GetObjectDocInfo(string parentId)
        {
            string strSql = @"select * from objectdocinfo t where t.parentid='{0}'";
            return OracleHelper.Query(string.Format(strSql, parentId)).Tables[0];
        }

        /// <summary>
        /// 删除文档
        /// add:Wangjh 20211105
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public bool DeleteObjecDocInfo(string parentId, string m_TempPath)
        {
            DataTable dt = GetObjectDocInfo(parentId);
            m_TempPath = m_TempPath.Replace(@"Temp\", "");
            string delSql = "delete objectdocinfo where parentid='{0}' ";
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string path = dr["filepath"].ToString();
                    path = path.Substring(0, path.LastIndexOf("/"));

                    try
                    {
                        DirectoryInfo di = new DirectoryInfo(m_TempPath + path);

                        di.Delete(true);

                    }
                    catch (Exception ex)
                    {
                    }
                }

            }
            OracleHelper.ExecuteSql(string.Format(delSql, parentId));
            return true;

        }

        /// <summary>
        /// 保存对象文档信息数据
        /// add:Wangjh 20211101
        /// </summary>
        /// <param name="parentid"></param>
        /// <param name="filepath"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public bool SaveObjectDocInfo(string parentid, string filepath, string filename)
        {
            string strSql = @"INSERT INTO OBJECTDOCINFO ob (ob.objectdocinfoid,ob.parentid,ob.filepath,ob.filename)
VALUES(sys_guid(),'{0}','{1}','{2}')";

            strSql = string.Format(strSql, parentid, filepath, filename);

            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 复制文信息
        /// </summary>
        /// <param name="sourceId"></param>
        /// <param name="targetId"></param>
        /// <returns></returns>
        public bool SaveASObjectDocInfo(string sourceId, string targetId)
        {
            string strSql = @"BEGIN  
FOR r IN (SELECT * FROM OBJECTDOCINFO WHERE parentid='{0}')
  LOOP
    INSERT INTO OBJECTDOCINFO ob (ob.objectdocinfoid,ob.parentid,ob.filepath,ob.filename)
    VALUES (sys_guid(),'{1}',r.filepath,r.filename);
  END LOOP
COMMIT;
END;";
            strSql = string.Format(strSql, sourceId, targetId);
            int i = OracleHelper.ExecuteSql(strSql);
            if (i > 0)
                return true;
            else
                return false;

        }

        /// <summary>
        /// 存入工序设备信息，暂存5个根据ly2equipment字段按,解析例如:设备001$设备型号001,设备002$设备型号002
        /// add:Wangjh 20211214
        /// </summary>
        public void SaveSpecResourceByly2equipment(string specname, string revision)
        {
            DataTable dt = GetRDOTable("spec", "specname", specname, "specrevision", revision);
            if (dt.Rows.Count == 0)
                return;
            string ly2equipment = dt.Rows[0]["ly2equipment"].ToString();
            if (string.IsNullOrWhiteSpace(ly2equipment))
                return;

            var res = ly2equipment.Split(',');
            if (res.Length == 0)
                return;

            string specid = dt.Rows[0]["specid"].ToString();
            string strSql = @"SELECT rf.resourcefamilyname,rd.resourcename,rd.description,rd.Resourceid FROM resourcedef rd
LEFT JOIN resourcefamily rf ON rf.resourcefamilyid=rd.resourcefamilyid
WHERE rf.resourcefamilyname LIKE '%{0}%' AND rd.description LIKE '%{1}%'
ORDER BY rd.resourceid DESC";

            string insertSql = @"insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') ";

            ArrayList insertSqls = new ArrayList();
            foreach (var re in res)
            {
                if (re.Split('$').Length != 2)
                    continue;
                if (insertSqls.Count >= 5)
                {
                    break;
                }
                DataTable reDt = OracleHelper.Query(string.Format(strSql, re.Split('$')[1], re.Split('$')[0])).Tables[0];

                if (reDt.Rows.Count == 0)
                    continue;

                foreach (DataRow dr in reDt.Rows)
                {
                    if (insertSqls.Count >= 5)
                    {
                        break;
                    }

                    insertSqls.Add(string.Format(insertSql, dr["Resourceid"].ToString(), specid));
                }

            }

            OracleHelper.ExecuteSqlTran(insertSqls);

        }

        /// <summary>
        ///特殊过程工序确认
        ///update:wangjh 20221129
        /// </summary>
        /// <param name="MainSCS"></param>
        /// <param name="detailSCS"></param>
        /// <returns></returns>
        public bool InsertSpecialCourseSpecToMES(DataTable MainSCS, DataTable detailSCS, string workflowId, string specId, string stepId)
        {
            System.Collections.ArrayList alist = new System.Collections.ArrayList();
            var mainId = MainSCS.Rows[0]["SpecialCourseSpecReportID"].ToString();
            foreach (DataRow row in MainSCS.Rows)
            {
                var selectSQL = string.Format("Select * from SpecialCourseSpecReport where SpecialCourseSpecReportNo='{0}' ", row["SpecialCourseSpecReportNo"].ToString());
                var tempDt = OracleHelper.Query(selectSQL).Tables[0];
                if (tempDt.Rows.Count > 0)//修改
                {
                    mainId = tempDt.Rows[0]["SpecialCourseSpecReportID"].ToString();
                    var updateSQL = "Update SpecialCourseSpecReport SET EmployeeNames='{0}',ResourceNames='{1}',InDate={2} where SpecialCourseSpecReportID='{3}'";
                    alist.Add(string.Format(updateSQL, row["EmployeeNames"], row["ResourceNames"], string.IsNullOrWhiteSpace(row["InDate"].ToString()) ? "''" : "to_date('" + row["InDate"] + "','yyyy-MM-dd hh24:mi:ss')", mainId));
                }
                else
                {
                    var insertSQL = "INSERT INTO SpecialCourseSpecReport(SpecialCourseSpecReportID,SpecialCourseSpecReportNo,EmployeeNames,ResourceNames,InDate) VALUES('{0}','{1}','{2}','{3}',{4})";
                    alist.Add(string.Format(insertSQL, mainId, row["SpecialCourseSpecReportNo"], row["EmployeeNames"], row["ResourceNames"], string.IsNullOrWhiteSpace(row["InDate"].ToString()) ? "''" : "to_date('" + row["InDate"] + "','yyyy-MM-dd')"));
                }
            }
            //参数明细，统一先删除再插入
            alist.Add(string.Format("delete SpecialCourseSpecParas where SpecialCourseSpecReportID='{0}'", mainId));
            foreach (DataRow row in detailSCS.Rows)
            {
                var insertSQL = "INSERT INTO SpecialCourseSpecParas(SpecialCourseSpecReportID,SpecialCourseSpecParasID,ParaName,ParaValue) VALUES('{0}','{1}','{2}','{3}')";
                alist.Add(string.Format(insertSQL, mainId, row["SpecialCourseSpecParasID"], row["ParaName"], row["ParaValue"]));
            }
            //存映射信息
            alist.Add(string.Format("delete SpecialCourseSpecMapp where SpecialCourseSpecReportID='{0}' and WorkflowID='{1}' and WorkflowStepID='{2}'", mainId, workflowId, stepId));
            alist.Add(string.Format(@"insert into SpecialCourseSpecMapp(SpecialCourseSpecMappID,SpecialCourseSpecReportID,WorkflowID,SpecID,WorkflowStepID,CreateDate)values(
            sys_guid(),'{0}','{1}','{2}','{3}',sysdate)", mainId, workflowId, specId, stepId));
            OracleHelper.ExecuteSqlTran(alist);
            return true;
        }

        /// <summary>
        /// 释放特殊过程批次暂停
        /// add by zhangrj 2022-12-05
        /// </summary>
        /// <param name="workflowID"></param>
        /// <returns></returns>
        public bool UpdateSpecialCourseContainerHold(string workflowID)
        {
            //查询该工艺路线下的所有已特殊过程暂停且特殊过程记录有效的批次，并将其释放
            string strSql = @"
SELECT DISTINCT c.containerid,c.containername,c.productid,c.productname,c.qty
from containercurrentinfo c
LEFT JOIN workflowstep ws ON ws.workflowstepid = c.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = nvl(sb.revofrcdid,ws.specid)
LEFT JOIN SpecialCourseSpecMapp scs ON scs.specid = s.specid AND scs.workflowid = c.workflowid
LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid AND SYSDATE >= ssr.indate 
LEFT JOIN 
(SELECT * FROM (SELECT o.*, row_number() OVER(ORDER BY o.createdate DESC) row_index FROM OperContainerRecord o)r WHERE r.row_index=1) t
ON t.containerid = c.containerid AND t.operreason = '特殊过程工序暂停'
WHERE c.isinstore = 0 AND c.ishold = 1 AND c.containerstatus = 1 AND c.workflowid = '{0}'
AND ssr.specialcoursespecreportid IS NOT NULL AND t.containerid IS NOT NULL";
            strSql = string.Format(strSql, workflowID);
            DataTable conDt = OracleHelper.Query(strSql).Tables[0];
            ArrayList sqlList = new ArrayList();
            foreach (DataRow item in conDt.Rows)
            {
                //插入释放记录
                strSql = @"INSERT INTO OperContainerRecord(containerid,containername,productid,productname,operatorid,operreason,qty,createdate,opertype)
                         VALUES(";
                strSql += "'" + item["containerid"].ToString() + "',";
                strSql += "'" + item["containername"].ToString() + "',";
                strSql += "'" + item["productid"].ToString() + "',";
                strSql += "'" + item["productname"].ToString() + "',";
                strSql += "'','特殊过程工序释放',";
                strSql += "'" + item["qty"].ToString() + "',";
                strSql += "sysdate,'3')";//释放
                sqlList.Add(strSql);
                //更新批次暂停状态
                strSql = "UPDATE containercurrentinfo cu SET cu.ishold = '0' WHERE cu.containerid = '" + item["containerid"].ToString() + "'";
                sqlList.Add(strSql);
            }
            if (sqlList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqlList);
            }
            return true;
        }
        /// <summary>
        /// 处理因为件号或工艺规程版本发生变更时，在制批次需要暂停的情况
        /// add by longb 2023-03-13
        /// </summary>
        /// <param name="productName"></param>
        /// <param name="productRev"></param>
        /// <param name="workflowName"></param>
        /// <param name="workflowRev"></param>
        /// <param name="specNo"></param>
        public void suspendContainer(string productName, string productRev, string workflowName, string workflowRev, List<string> specNoList)
        {
            var strSpecNos = "'" + string.Join(",", specNoList).Replace(",", "','") + "'";
            var strRecordSql = @"SELECT distinct SPECNO FROM CONTAINERSTEPRECORD c WHERE c.ContainerStepRecordID is not null and c.SPECNO  IN (" + strSpecNos + ")";
            if (!string.IsNullOrWhiteSpace(productName))
            {
                strRecordSql += string.Format(" and c.productName='{0}' and c.productrevision!='{1}' ", productName, productRev);
            }

            if (!string.IsNullOrWhiteSpace(workflowName))
            {
                strRecordSql += string.Format(" and c.workflowname='{0}' and c.workflowrevision!='{1}' ", workflowName, workflowRev);
            }

            DataTable dt = OracleHelper.Query(strRecordSql).Tables[0];
            //查询批次工序加工情况，报工工序个数 < 需要暂停工序个数，可暂停工序批次
            if (dt.Rows.Count <= specNoList.Count)
            {
                string strSql = @"select ut.attributevalue,cci.* from containercurrentinfo cci 
                        LEFT JOIN userattribute ut ON ut.userattributename='分厂代码' AND ut.parentid=cci.originalfactoryid
                        WHERE  cci.isinstore=0 AND NVL(CCI.ishold,0)=0 ";// ut.attributevalue='601' and

                if (!string.IsNullOrWhiteSpace(productName))
                {
                    strSql += string.Format(" and cci.productName='{0}' and cci.productrevision!='{1}' ", productName, productRev);
                }

                if (!string.IsNullOrWhiteSpace(workflowName))
                {
                    strSql += string.Format(" and cci.workflowname='{0}' and cci.workflowrevision!='{1}' ", workflowName, workflowRev);
                }

                DataTable conDt = OracleHelper.Query(strSql).Tables[0];

                //暂停所查出的批次
                ArrayList sqls = new ArrayList();
                string sql = "update containercurrentinfo set ishold=1 where containerid='{0}'";
                string sql2 = @"INSERT INTO OperContainerRecord(containerid,containername,productid,productname,operatorid,operreason,qty,createdate,opertype)
                                VALUES('{0}','{1}','{2}','{3}','{4}','产品工艺变更暂停',{5},SYSDATE,'2')";
                foreach (DataRow dr in conDt.Rows)
                {
                    sqls.Clear();
                    sqls.Add(string.Format(sql, dr["containerid"].ToString()));
                    sqls.Add(string.Format(sql2, dr["containerid"].ToString(), dr["containername"].ToString(), dr["productid"].ToString(), dr["productname"].ToString(), "", dr["qty"].ToString()));
                    OracleHelper.ExecuteSqlTran(sqls);
                }
            }
        }

        /// <summary>
        /// 根据件号和版本查询相关的所有虚拟子件号
        /// add:Wangjh 20230628
        /// </summary>
        /// <param name="name"></param>
        /// <param name="rev"></param>
        /// <returns></returns>
        public DataTable GetSubProduct(string name, string rev)
        {
            return OracleHelper.Query(string.Format(@"SELECT pb2.productname,p2.productrevision,p2.productid,pt.producttypename,(SELECT COUNT(1) FROM userattribute  ut WHERE ut.parentid=p2.productid) AttributeCount  FROM product p 
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
INNER JOIN product p2 ON p2.workflowroute=pb.productname AND p2.productrevision=p.productrevision
LEFT JOIN productbase pb2 ON pb2.productbaseid=p2.productbaseid
left JOIN producttype pt ON pt.producttypeid=p2.producttypeid
WHERE pb.productname='{0}' AND p.productrevision='{1}'", name, rev)).Tables[0];
        }

        /// <summary>
        /// 查询工艺规程和工序底下所有子工艺信息
        /// add:Wangjh 20240708
        /// </summary>
        /// <param name="workflowName"></param>
        /// <param name="workflowStepName"></param>
        /// <returns></returns>
        public DataTable GetSubWorkflowStep(string workflowName, string workflowStepName)
        {
            string strSql = @"select wb.workflowname,ws.workflowstepname,ss.* from workflow w 
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
inner join stepsubstep ss on ss.workflowid=w.workflowid
left join workflowstep ws on ws.workflowstepid=ss.workflowstepid
where 1=1 ";
            if (!string.IsNullOrWhiteSpace(workflowName))
            {
                strSql += string.Format(" and wb.workflowname='{0}' ", workflowName);
            }
            if (!string.IsNullOrWhiteSpace(workflowStepName))
            {
                strSql += string.Format(" and ws.workflowstepname='{0}' ", workflowStepName);
            }
            strSql += "order by w.workflowid desc,ws.workflowstepid";
            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 根据工艺id查询工步信息
        /// add:wangjh 20240910
        /// </summary>
        /// <param name="workflowId"></param>
        /// <returns></returns>
        public DataTable GetStepInfoByWorkflowId(string workflowId)
        {
            string sql = @"SELECT wb.workflowname,w.workflowrevision,w.workflowid,ws.workflowstepname,s.specrevision,sfb.stepinfoname,sf.stepinfoid,sf.description stepinfodesc,
sf.stepseq,sf.revision stepinforev,sf.makecycle,sf.preparecycle,sf.shipcycle,sf.steptype,sf.transfercycle,sf.chargingqty,sf.workcenterid,sf.teamid
FROM workflow w
LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
LEFT JOIN spec s ON s.specid=ws.specid
LEFT JOIN stepinfo sf ON sf.workflowstepid=ws.workflowstepid
LEFT JOIN stepinfobase sfb ON sfb.stepinfobaseid=sf.stepinfobaseid";
            sql += string.Format(" where w.workflowid='{0}' ", workflowId);
            sql += " ORDER BY ws.sequence,sf.stepseq ";
            return OracleHelper.Query(sql).Tables[0];
        }
        /// <summary>
        /// 根据工艺id查询工步设备信息
        /// add:wangjh 20241113
        /// </summary>
        /// <param name="workflowId"></param>
        /// <returns></returns>
        public DataTable GetStepInfoResource(string workflowId)
        {
            string sql = @"SELECT wb.workflowname,w.workflowrevision,w.workflowid,ws.workflowstepname,sfb.stepinfoname,sf.stepinfoid,sf.description stepinfodesc,
sf.stepseq,sf.revision stepinforev,sc.resourceid
FROM workflow w
LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid 
LEFT JOIN stepinfo sf ON sf.workflowstepid=ws.workflowstepid
LEFT JOIN stepinfobase sfb ON sfb.stepinfobaseid=sf.stepinfobaseid
INNER JOIN stepresourceinfo sc ON sc.stepid=sf.stepinfoid";
            sql += string.Format(" where w.workflowid='{0}' ", workflowId);
            sql += " ORDER BY ws.sequence,sf.stepseq ";
            return OracleHelper.Query(sql).Tables[0];
        }

        /// <summary>
        /// 更新工步的期量信息
        /// add:wangjh 20240910
        /// </summary>
        /// <param name="dr"></param>
        public void UpdateStepInfoTime(DataRow dr)
        {
            string strSql = @"UPDATE STEPINFO SF SET SF.MAKECYCLE='{1}',SF.preparecycle='{2}',SF.shipcycle='{3}',SF.steptype='{4}',SF.TRANSFERCYCLE='{5}',SF.CHARGINGQTY='{6}',SF.WORKCENTERID='{7}',SF.TEAMID='{8}' 
WHERE SF.STEPINFOID='{0}'";

            strSql = string.Format(strSql, dr["STEPINFOID"].ToString(), dr["MAKECYCLE"].ToString(), dr["preparecycle"].ToString(), dr["shipcycle"].ToString(), dr["steptype"].ToString(), dr["TRANSFERCYCLE"].ToString(), dr["CHARGINGQTY"].ToString(), dr["WORKCENTERID"].ToString(), dr["TEAMID"].ToString());

            OracleHelper.ExecuteSql(strSql);
        }
        /// <summary>
        /// 判断工厂下是否存在对应的工区
        /// </summary>
        /// <param name="factoryCode">分厂编码</param>
        /// <param name="workCenterName">工区名称</param>
        /// <returns></returns>
        public bool IsExistWorkCenter(string factoryCode,string workCenterName)
        {
            var workcenterSQL = @"select w.workcenterid from workcenter w 
                   left join factory f on f.factoryid =w.factoryid 
                   left join userattribute u on u.parentid =f.factoryid and u.userattributename ='分厂代码'
                   where u.attributevalue ='{0}' and w.workcentername ='{1}'";
            var re = OracleHelper.GetSingle(string.Format(workcenterSQL, factoryCode, workCenterName));
            if (re == null)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 判断工厂下是否存在对应的班组
        /// </summary>
        /// <param name="factoryCode">分厂编码</param>
        /// <param name="workCenterName">工区名称</param>
        /// <returns></returns>
        public bool IsExistTeam(string factoryCode, string teamName)
        {
            var workcenterSQL = @"select w.teamid from team w 
                   left join factory f on f.factoryid =w.factoryid 
                   left join userattribute u on u.parentid =f.factoryid and u.userattributename ='分厂代码'
                   where u.attributevalue ='{0}' and w.teamname ='{1}'";
            var re = OracleHelper.GetSingle(string.Format(workcenterSQL, factoryCode, teamName));
            if (re == null)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 批量执行SQL语句
        /// add:wangjh 20241113
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {
            if (SQLStringList.Count > 0)
                OracleHelper.ExecuteSqlTran(SQLStringList);
        }
        /// <summary>
        /// 根据sql查询Datatable数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Query(string sql) 
        {
            DataTable dt = null;
            try {
                dt = OracleHelper.Query(sql, null).Tables[0];
            }
            catch (Exception ex) { dt = null; }
            return dt;
        }
        /// <summary>
        /// 根据sql查询单条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetSingle(string sql) 
        {
            return OracleHelper.GetSingle(sql);
        }

        /// <summary>
        /// 更新旧工艺上的字段到新工艺上
        /// add:wangjh 20250307
        /// </summary>
        /// <param name="workflowId"></param>
        /// <param name="oldWorkflowId"></param>
        public void updateWorkflowFromOldWf(string workflowId,string oldWorkflowId) {
            string selSql = @"select w.image from workflow w where w.workflowid='{0}'";
            string updateSql = @"update  workflow w set w.image='{1}' where w.workflowid='{0}'";

            DataTable dt = OracleHelper.Query(string.Format (selSql,oldWorkflowId)).Tables[0];
            if (dt.Rows.Count == 0)
                return;

            OracleHelper.ExecuteSql(string.Format(updateSql, workflowId, dt.Rows[0]["image"].ToString()));

        }
        
        /// <summary>
        /// 根据件号、工艺及其版本--查询其他版本所有的件号数据
        /// add by longb 2025-06-05
        /// </summary>
        /// <param name="strProductName">件号</param>
        /// <param name="strWorkflow">工艺</param>
        /// <param name="strWorkflowRev">工艺版本</param>
        /// <returns></returns>
        public DataTable GetAllProductByWorkflowAndProductName(string strProductName,string strWorkflow,string strWorkflowRev) 
        {
            string strSql = string.Format(@"select distinct pb.productname,p.productrevision,w.workflowtype2--,wb.workflowname,w.workflowrevision, 
                            from productworkflowmapping pm 
                            left join product p on p.productid =pm.productid 
                            left join productbase pb on pb.productbaseid =p.productbaseid 
                            left join workflow w on w.workflowid =pm.workflowid 
                            left join workflowbase wb on wb.workflowbaseid =w.workflowbaseid 
                            where pb.productname ='{0}' and wb.workflowname='{1}' and w.workflowrevision<>'{2}'
                            order by p.productrevision", strProductName,strWorkflow,strWorkflowRev);
            DataTable dt = new DataTable();
            try
            {
                dt = OracleHelper.Query(strSql).Tables[0];
            }
            catch { }
            return dt;
        }
    }
}
